Left Join and Aggregates

[lespaul]

I need to be able to return all the judges in CMPJUDNM even when there is no corresponding data in CMPHERMF. I thought that a left or right join would work, but I think since I'm using aggregate functions it's not working as I expected.

SELECT J.JUDCOD, J.JUDNAM, H.DISPO, COUNT(H.DISPO) 
FROM CMPJUDNM J
LEFT OUTER JOIN CMPHERMF H ON J.JUDCOD = H.JUDCOD
GROUP BY J.JUDCOD, J.JUDNAM, H.DISPO

CMPJUDNM:
JUDCOD     JUDNAM
1          Victor Valdez
2          Kevin Fitzwater
3          Cristina Jaramillo


CMPHERMF
CASPRE     CASNUM     DISPO      JUDCOD
CR         123405      GU          1
CR         123505      NG          1
TR          32105      DI          1
TR          32305      GU          1
CR         321405      GU          3
CR         456505      NG          3
DW         98704       DE          3


So my results would be:

1          Victor Valdez         GU         2
1          Victor Valdez         NG         1
1          Victor Valdez         DI         1
2          Kevin Fitzwater
3          Cristina Jaramillo    GU         1
3          Cristina Jaramillo    NG         1
3          Cristina Jaramillo    DE         1

does anyone know if this is possible in a query?

thanks!

leslie

 

[ddiamond]

Your query looks good to me. What results are you getting?

 

[lespaul]

I currently just get the judges with data:

1          Victor Valdez         GU         2
1          Victor Valdez         NG         1
1          Victor Valdez         DI         1
3          Cristina Jaramillo    GU         1
3          Cristina Jaramillo    NG         1
3          Cristina Jaramillo    DE         1

I need the "blank" ones too:

1          Victor Valdez         GU         2
1          Victor Valdez         NG         1
1          Victor Valdez         DI         1
[b]2          Kevin Fitzwater[/b]
3          Cristina Jaramillo    GU         1
3          Cristina Jaramillo    NG         1
3          Cristina Jaramillo    DE         1

 

[juliane26]

I just tried it, on Windows v8.2 I am getting the expected result ...

db2 => select * from cmpjudnm

JUDCOD      JUDNAM

----------- -----------------------------------------------
          1 Victor Valdez
          2 Kevin Fitzwater
          3 Cristina Jaramillo

  3 record(s) selected.

db2 => select * from cmphermf

CASPRE CASNUM               DISPO JUDCOD
------ -------------------- ----- -----------
CR                   123405 GU              1
CR                   123505 NG              1
TR                    32105 DI              1
TR                    32305 GU              1
CR                   321405 GU              3
CR                   456505 NG              3
DW                    98704 DE              3

  7 record(s) selected.

db2 => select J.JUDCOD, substr(J.JUDNAM,1,30), H.DISPO, COUNT(H.DISPO) FROM CMPJUDNM J LEFT OUTER JO
IN CMPHERMF H ON J.JUDCOD = H.JUDCOD GROUP BY J.JUDCOD, J.JUDNAM, H.DISPO order by judcod

JUDCOD      2                              DISPO 4
----------- ------------------------------ ----- -----------
          1 Victor Valdez                  DI              1
          1 Victor Valdez                  GU              2
          1 Victor Valdez                  NG              1
          2 Kevin Fitzwater                -               0
          3 Cristina Jaramillo             DE              1
          3 Cristina Jaramillo             GU              1
          3 Cristina Jaramillo             NG              1

I added an order by just to have it ordered, with or without , Kevin would be there ...


Juliane

 

[BrianTyler]

Leslie,

Was this the actual SQL that you were running. i have often made the mistake of using a left outer join but then referencing some field of the optional table in a 'where' statemet. This will often onoit the ros unless youcater for the null conditions.

Brian

 

[lespaul]

Brian - No this isn't the actual SQL, our database is so unnormalized (see Thread655-606833 for a good laugh on bad databases).

The actual SQL is big and long and ugly.

I've come up with a way to do what I need in the code that's processing the query results. I'm actually used to doing it that way (my co-worker & I call the database a "cluebase" - extract all the clues and put them together to get the data that you really need!), I was just trying to get it all in the query so there would be less processing.
I'm sure that I'm excluding the nulls in some other part of the query...nice to know that the left join SHOULD have worked!

thanks for your input,

Leslie