Showing row for title when no data exists

[scoobyroo]

I have a report that needs to show the Partners, Paralegals and Associates even if one of those titles has no data (the row would be blank except for the title).

I have the the sql statement below, but can't figure out how to make a title show up in the results when no data exists for it. ANy help would be appreciated.


SELECT CASE WHEN watitle = 'PARTNER' THEN 'Partner'
WHEN watitle in ('PARALEGAL','CASE ASSISTANT') THEN 'Paralegal'
WHEN watitle in ('ASSOCIATE','CONTRACT ATTY','OF COUNSEL','SENIOR COUNSEL','LAW CLERK','PATENT AGENT') THEN 'Associate'
END Title
, w.waloc
, l.ldesc
, w.wadept
, d.head1
, w.wasect
, t.tsectdes
, SUM(w.hwacur) Hours
FROM SVCDATAMAGIC.son_db.dbo.df_dwwatty w
INNER JOIN SVCELITEDB.son_db.dbo.eis_period p ON w.pe = p.per
INNER JOIN SVCELITEDB.son_db.dbo.deptlab d ON w.wadept = d.delcode
INNER JOIN SVCELITEDB.son_db.dbo.tsection t ON w.wasect = t.tsection
INNER JOIN SVCELITEDB.son_db.dbo.location l ON w.waloc = l.locode
WHERE w.watitle in ('PARTNER','PARALEGAL','CASE ASSISTANT','ASSOCIATE','CONTRACT ATTY','OF COUNSEL','SENIOR COUNSEL','LAW CLERK','PATENT AGENT')
AND p.period = 200806
GROUP BY CASE WHEN watitle = 'PARTNER' THEN 'Partner'
WHEN watitle in ('PARALEGAL','CASE ASSISTANT') THEN 'Paralegal'
WHEN watitle in ('ASSOCIATE','CONTRACT ATTY','OF COUNSEL','SENIOR COUNSEL','LAW CLERK','PATENT AGENT') THEN 'Associate'
END
, w.waloc
, l.ldesc
, w.wadept
, d.head1
, w.wasect
, t.tsectdes

 

[bborissov]

Use LEFT instead of INNER JOIN and handle NULL data.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.