Results 1 to 10 of 10

Thread: Query Help

  1. #1
    Join Date
    Sep 2010
    Posts
    14

    Unanswered: Query Help

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with the query you got?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    14
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2010
    Posts
    14
    Ok you are freakin awesome!!! I am buying your book now...

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


  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this message too short

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2010
    Posts
    14
    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'

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2010
    Posts
    14
    No Sorry; this is the first time I am seeing this. I have only been working with SQL for about 4 months

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so after you looked up the COALESCE function in the manual, you now understand how it works, right?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •