Joining and Conditional Column Data Help

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.

employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)

If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!

SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc

SELECT

e.empid,

e

.empname,

COUNT

(t.taskid)as'tcount'

FROM

tasks t

LEFT

JOIN employee eON e.empid= t.empid

WHERE

t.resolutionISNULL

GROUP

BY e.empid, e.empname

Order

by tcountDesc|||

limno's query doesn't quite work the way you'd expect. Because it is being filtered by where the resolution is null from within the WHERE clause, it will eliminate employees that have no records where resolution is null from the output. If you want the employees listed even if they have no tasks, then use this instead:

SELECT empid, empname, (SELECTCOUNT(*)FROM tasksWHERE tasks.empid=employee.empidAND resolutionISNULL)as'tcount'FROM employeeOrder by tcountDesc

|||

Thanks. That helped me put together someting else

selecte.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution isnull and t.empid is not null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc

Join Query Help

Thank you for your help. I hope this makes sense.
I have a table that contains 8 rows. Each row describes a phone type
(office, voice, etc) with a primary key and description, thats all.
Another table contains customer rows (first name, last, etc), and a primary
key, customerid.
A third table contains phone numbers as they relate to customers. A PhoneId
field is the primary key for this table. The customerid and the phone type
primary key are foreign keys in this table.
A customer can have many phone types. I am trying to write a query that gets
the existing phone types with phone, along with those phone types that the
customer does NOT have. My WHERE clause in this query is what I think is
breaking it.
A full outer join is not accomplishing this. I only get those numbers that
already are assigned to the customer. Since there are 8 types of numbers I
expect 8 rows.
SELECT
p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
FROM Phones ph
FULL OUTER JOIN PhoneTypes p
on ph.PhoneTypeFK = p.PhoneTypePK
WHERE ph.CustomerID = 12345
How can I get all 8 rows?never mind guys... I got it as soon as I posted it. I am now doing an inner
query that returns the phone numbers assigned and FULL OUTER JOINing that
with an outer query that retrieves all my phone types on the PhoneTypeFK.
Thanks
"Learning SQL Server" <no.mail.com> wrote in message
news:OsLsLaEXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Thank you for your help. I hope this makes sense.
> I have a table that contains 8 rows. Each row describes a phone type
> (office, voice, etc) with a primary key and description, thats all.
> Another table contains customer rows (first name, last, etc), and a
primary
> key, customerid.
> A third table contains phone numbers as they relate to customers. A
PhoneId
> field is the primary key for this table. The customerid and the phone type
> primary key are foreign keys in this table.
> A customer can have many phone types. I am trying to write a query that
gets
> the existing phone types with phone, along with those phone types that the
> customer does NOT have. My WHERE clause in this query is what I think is
> breaking it.
> A full outer join is not accomplishing this. I only get those numbers that
> already are assigned to the customer. Since there are 8 types of numbers I
> expect 8 rows.
> SELECT
> p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
> FROM Phones ph
> FULL OUTER JOIN PhoneTypes p
> on ph.PhoneTypeFK = p.PhoneTypePK
> WHERE ph.CustomerID = 12345
> How can I get all 8 rows?
>
>|||even better - a simple left join does the trick.
"Learning SQL Server" <no.mail.com> wrote in message
news:OsLsLaEXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Thank you for your help. I hope this makes sense.
> I have a table that contains 8 rows. Each row describes a phone type
> (office, voice, etc) with a primary key and description, thats all.
> Another table contains customer rows (first name, last, etc), and a
primary
> key, customerid.
> A third table contains phone numbers as they relate to customers. A
PhoneId
> field is the primary key for this table. The customerid and the phone type
> primary key are foreign keys in this table.
> A customer can have many phone types. I am trying to write a query that
gets
> the existing phone types with phone, along with those phone types that the
> customer does NOT have. My WHERE clause in this query is what I think is
> breaking it.
> A full outer join is not accomplishing this. I only get those numbers that
> already are assigned to the customer. Since there are 8 types of numbers I
> expect 8 rows.
> SELECT
> p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
> FROM Phones ph
> FULL OUTER JOIN PhoneTypes p
> on ph.PhoneTypeFK = p.PhoneTypePK
> WHERE ph.CustomerID = 12345
> How can I get all 8 rows?
>
>