Query Help

lightfju · #1 (**permalink**) 10-20-11, 15:55

I am trying to pull a list of folks and only want to know if they have a son or daughter. Let's say they have a dad, I would not want his name to pull but I still would want to know the clients name. In other words I only want to know every client and what their kids names are.

I am using the following:

Code:

SELECT
placements.program AS Program,
client.cnum AS `SDS Client #`,
client.cname AS Client,
progstay.bdate AS `Program Admission`,
progstay.edate AS `Program Discharge`,
client_coresident.crname,
client_coresident.relationship
FROM
client
Inner Join placements ON client.cnum = placements.cnum
Inner Join progstay ON progstay.cnum = client.cnum
Left Join client_coresident ON client.cnum = client_coresident.cnum
WHERE
placements.program =  'HST' AND
((placements.place_edt >=  '2011/07/01' AND
placements.place_edt <=  '2012/06/30') OR
placements.place_edt IS NULL ) AND
(client_coresident.relationship = 'Son' OR
client_coresident.relationship = 'daughter')
ORDER BY
Client ASC

r937 · #2 (**permalink**) 10-20-11, 22:32

what's wrong with the query you got?

lightfju · #3 (**permalink**) 10-21-11, 09:50

The issue is, that not everyone comes up. Lets say that there are 3 people. Person 1 & 2 have a son or daughter and person 3 does not. I still want person 3 name to show even though they do not have a child and have a blank in that column. Let's add one more level here, and say that I have a husband listed with all 3 and don't want their names to show in the final report

r937 · #4 (**permalink**) 10-21-11, 09:57

move the client_coresident conditions out of the WHERE clause (where they effectively change the join to an inner join) and into the ON clause of the LEFT OUTER JOIN (where they will support the join's outerness)

try this --

Code:

SELECT placements.program AS Program
     , client.cnum AS `SDS Client #`
     , client.cname AS Client
     , progstay.bdate AS `Program Admission`
     , progstay.edate AS `Program Discharge`
     , client_coresident.crname
     , client_coresident.relationship
  FROM placements 
INNER 
  JOIN client
    ON client.cnum =  placements.cnum
INNER 
  JOIN progstay 
    ON progstay.cnum = client.cnum
LEFT OUTER
  JOIN client_coresident 
    ON client_coresident.cnum = client.cnum
   AND client_coresident.relationship 
       IN( 'Son' , 'daughter')
 WHERE placements.program =  'HST' 
   AND COALESCE(placements.place_edt,'2011/07/01')
       BETWEEN '2011/07/01' AND '2012/06/30'
ORDER 
    BY client.cname ASC

lightfju · #5 (**permalink**) 10-21-11, 10:09

Ok you are freakin awesome!!! I am buying your book now...

(No, for real right now it is downloading to my Kindle)

r937 · #6 (**permalink**) 10-21-11, 10:19

this message too short

lightfju · #7 (**permalink**) 10-21-11, 10:57

Ok one more question:

Can you explain this part of the code to me?

Code:

  AND COALESCE(placements.place_edt,'2011/07/01')
       BETWEEN '2011/07/01' AND '2012/06/30'

r937 · #8 (**permalink**) 10-21-11, 11:14

it's just a tighter way of writing what you had in the original query --

AND
((placements.place_edt >= '2011/07/01' AND
placements.place_edt <= '2012/06/30') OR
placements.place_edt IS NULL )

you know how COALESCE works, right?