Left Join not working as expected 1

[lespaul]

I'm trying to query a DB2 AS400/iSeries database from Delphi, but I'm pretty sure it's an SQL problem (and you guys are the best at solving SQL problems!!!). I have the following tables:

[b]JMPSTATUS[/b]
STCODE    STDESC
  WS      Will Serve
  PP      Postponed
  IC      Ineligible Citizen
  EM      Excused Medical

[b]JMPMAIN[/b]
JURNUM   etc....      STATUSCD ......  ORGPOST
12345                   WS             20050110
12346                   PP             20050110
12347                   EM             20050110
12348                   WS             20050110
12349                   PP             20050110
12350                   WS             20050110

I am trying to get a totals query counting the number of jurors for each status code. But, I want all the status codes included in the result whether or not a juror has been assigned that code for the specified term.

So for the examples above, I would like to return:

WS      3
PP      2
IC      (blank or 0)
EM      1

I have tried:

SELECT STCODE, COUNT(*) FROM JMPMAIN M LEFT OUTER JOIN JMPSTATUS S ON S.STCODE = M.STATUSCD WHERE ORGPOST = '20050110' GROUP BY STCODE

which I expected to return all the records from JMPSTATUS, but it doesn't it returns (actual data):

NR      11
PS      5
PP      16
RU      27
ES      1
EH      3
EM      19
IR      7
IC      5
IF      6
OT      4
WS      49

but this listing is missing status codes AM and EC, EF (which have a zero total for the term date selected)

Can I not use the LEFT JOIN operation to get the expected results when using an aggregate function? I have tried selecting from JMPSTATUS and joining into JMPMAIN, I have used Right Joins, added and removed the OUTER keyword. I just don't know!!!

Thanks for any insight!

Leslie

 

[checkai]

SELECT STCODE, COUNT(M.JURNUM) 
FROM JMPSTATUS S 
LEFT OUTER JOIN JMPMAIN M ON S.STCODE = M.STATUSCD WHERE ORGPOST = '20050110' OR ORGPOST IS NULL
GROUP BY STCODE

try this...

??? Web Hosting Solutions[/LINK]

??? [url=http://www.suaffiliates.net]Affiliate Program[/LINK]

 

[lespaul]

Nope, ORGPOST is never null so that didn't change anything.

Thanks for looking!

les

 

[thysonj]

Are you sure you switched the two table names therefore making the left table JMPSTATUS? That should work. The reason the three don't come up in the first is that of course they don't exist in the join since they are not used by the table you are using as the left joinging table. If you switch the tables then even the unused ones should be joined with nulls for the joined table. I am betting you only added the extra where clause condition and didn't switch the tables around.

 

[Pattycake245]

Try This

SELECT S.STCODE, ISNULL(COUNT(M.STATUSCD),0) FROM JMPSTATUS S
LEFT OUTER JOIN
JMPMAIN M ON S.STCODE = M.STATUSCD AND ISNULL(M.ORGPOST,0) = '20050110' GROUP BY S.STCODE

Tim

 

[lespaul]

I had to modify it slightly to work with the DB2 syntax, but this worked correctly:

SELECT S.STCODE, COUNT(M.STATUSCD) FROM JMPSTATUS S
LEFT OUTER JOIN
JMPMAIN M ON S.STCODE = M.STATUSCD AND M.ORGPOST = '20050110' GROUP BY S.STCODE

is it because you are counting the STATUSCD and I was using *?

Thanks!

leslie