query

I have a query that I need help on. The cc_division table is a lookup. In my results I want to display all divisions regardless of the results. The statement works fine except I am limiting my results in the where clause. Can anyone tell me how I could do the exact thing below so that I can return all of the items in my lookup?
Thanks
Here is my SQL statement:
SELECT DISTINCT dbo.cc_division.division, dbo.cc_division.division_id, COUNT(dbo.cc_employee.employee_key) AS total
FROM dbo.cc_employee RIGHT OUTER JOIN
dbo.cc_division ON dbo.cc_employee.division_id = dbo.cc_division.division_id
WHERE (dbo.cc_employee.employee_key NOT IN
(SELECT employee_key
FROM cc_card
WHERE active = 1))
GROUP BY dbo.cc_division.division, dbo.cc_division.division_id
One way to do it would be like this:
SELECT DISTINCT dbo.cc_division.division, dbo.cc_division.division_id, COUNT(cc_employee.employee_key) AS total
FROM (SELECT *FROM dbo.cc_employee WHERE dbo.cc_employee.employee_key NOT IN
(SELECT employee_key
FROM cc_card
WHERE active = 1)) AS cc_employee RIGHTOUTER JOIN
dbo.cc_division ON cc_employee.division_id =dbo.cc_division.division_id
GROUP BY dbo.cc_division.division, dbo.cc_division.division_id
I haven't tested it so there might be syntax errors.
|||perfect thank you

query

Hello,
I wondered if anyone can help me? i'm trying to query a table, although
want to preform two searches on the same table/column then display the
results.. these are my two select statements. how can I join them
together so they display side by side.
SELECT COUNT(staff.sex) as "Number of Female members"
FROM staff
WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
SELECT COUNT(staff.sex) as "Number of Male members"
FROM staff
WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
Thanks for you help
The following example should return the desired results:
CREATE TABLE dbo.Staff
(
sex VARCHAR(10),
deptName VARCHAR(20)
)
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'Other department'
INSERT dbo.Staff SELECT 'Male', 'Other department'
SELECT COUNT(SEX) AS [Number of Male members],
(SELECT COUNT(SEX)
FROM dbo.staff
WHERE sex = 'Female'
AND deptName = 'CIS department') AS [Number of Female members]
FROM dbo.staff
WHERE sex = 'Male'
AND deptName = 'CIS department'
HTH
- Peter Ward
WARDY IT Solutions
"davidjohnlong@.googlemail.com" wrote:

> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>
|||If you would rather return your data in rows, you could try:
SELECT COUNT(sex) [Number of members],
CASE sex
WHEN 'Mmale' THEN 'Male'
WHEN 'Female' THEN 'Female'
ELSE 'Unknown'
END [Sex]
FROM staff
WHERE deptName = 'CIS department'
GROUP BY [sex]
|||davidjohnlong@.googlemail.com a crit :
> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>
SELECT SUM(CASE staff.sex
WHEN 'Female' THEN 1
WHEN 'Male' THEN 0
END) AS "Number of Female members",
SUM(CASE staff.sex
WHEN 'Female' THEN 0
WHEN 'Male' THEN 1
END) AS "Number of Male members"
FROM staff
WHERE staff.deptName = 'CIS department'
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

query

Hello,
I wondered if anyone can help me? i'm trying to query a table, although
want to preform two searches on the same table/column then display the
results.. these are my two select statements. how can I join them
together so they display side by side.
SELECT COUNT(staff.sex) as "Number of Female members"
FROM staff
WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
SELECT COUNT(staff.sex) as "Number of Male members"
FROM staff
WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
Thanks for you helpThe following example should return the desired results:
CREATE TABLE dbo.Staff
(
sex VARCHAR(10),
deptName VARCHAR(20)
)
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'Other department'
INSERT dbo.Staff SELECT 'Male', 'Other department'
SELECT COUNT(SEX) AS [Number of Male members],
(SELECT COUNT(SEX)
FROM dbo.staff
WHERE sex = 'Female'
AND deptName = 'CIS department') AS [Number of Female members]
FROM dbo.staff
WHERE sex = 'Male'
AND deptName = 'CIS department'
HTH
- Peter Ward
WARDY IT Solutions
"davidjohnlong@.googlemail.com" wrote:

> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>|||If you would rather return your data in rows, you could try:
SELECT COUNT(sex) [Number of members],
CASE sex
WHEN 'Mmale' THEN 'Male'
WHEN 'Female' THEN 'Female'
ELSE 'Unknown'
END [Sex]
FROM staff
WHERE deptName = 'CIS department'
GROUP BY [sex]|||davidjohnlong@.googlemail.com a crit :
> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>
SELECT SUM(CASE staff.sex
WHEN 'Female' THEN 1
WHEN 'Male' THEN 0
END) AS "Number of Female members",
SUM(CASE staff.sex
WHEN 'Female' THEN 0
WHEN 'Male' THEN 1
END) AS "Number of Male members"
FROM staff
WHERE staff.deptName = 'CIS department'
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Q: Tooltip

is it possible to display a field value in the tooltip. I put value there, it
does not show, it shows only text.Make sure the datatype of the tooltip expression is a string.
E.g. =CStr(Fields!SomeNumericField.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:BF011F4D-C810-4FB2-A61B-0EEA75EC3A84@.microsoft.com...
> is it possible to display a field value in the tooltip. I put value there,
it
> does not show, it shows only text.