Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2005
    Posts
    20

    Unanswered: Help with GROUP BY query....

    This is a fairly compliacted query and I just can't seem to figure it out.
    I will try to psuedocode it to see if anyone can figure out how to do it with sql.

    Table Descriptions:
    I have to join two tables. Houses and Inpsections. A house can have many inspections. A single inspection can only be done on 1 house.
    Houses: HouseID, HouseDescription
    Inspections: InspectionID, Inspection_date, House_Id, pass_inspection ("yes"/"no"),

    What I need:
    I need to have only 1 result per House. For each house, I need the results for the LATEST INSPECTION. That is pass_inspection, houseID, inspection_date for the last time the house was inspected ONLY (that is if its been inspected at all - if not i still need the house listed and say "not inspected yet").

    A query would be greatly appreciated.

    Thanks,

    DB

    If you want to go further (which I will need to do) I need actually do this for each pair of House + problem. Houses and problems have a many to many relationship.

    Problems: problem_id, problem_inspection
    house_problems: problem_id, houseID

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How do you fancy a natural English solution for you to write up? Just a thought as it will help you too.

    I would create a derived table from inspections, including the house_id and pass_inspection fields. I would create a where clause that filters against a corrolated sub query that is again based on the insepctions table and match the house ids and the maximum inspection date of this corrolated query to your first inspections table.

    Build & run that - you should have the last inspection of all houses that have been inspected.

    Once satisfied - left outer join from houses to your derived table. Et viola - all houses and their inspection result (if they have one).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I liked the sound of this problem so I thought I'd have a go.
    This is completely untested but it does work in my head
    Code:
    SELECT *
    FROM houses h
    INNER JOIN inspections i
    ON i.houseID = h.houseID
    WHERE i.inspection_date = 
    (SELECT max(inspection_date) FROM houses WHERE houseID = h.houseID)
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    LEFT OUTER JOIN, george, with an AND condition in the ON clause, not a WHERE condition

    otherwise, your correlated subquery is fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I know that you put conditions on the joins (not where clause) with outer joins - but why an outer join in this case? I thought it was a reasonable attempt

    You've taught me well Rudy
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    but why an outer join in this case?
    because the original poster said:
    (that is if its been inspected at all - if not i still need the house listed and say "not inspected yet")
    you must've just missed that part because it was in parentheses, eh

    you'll be wanting to use coalesce too

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

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM houses h
    LEFT OUTER JOIN inspections i
    ON i.houseID = h.houseID
    AND i.inspection_date = 
    (SELECT max(inspection_date) FROM houses WHERE houseID = h.houseID)
    However I don't see a field of the correct datatype to perform the coalesce to achieve "not inspected yet"
    unless...
    Code:
    SELECT h.*, i.*,
    CASE WHEN inspection_date IS NULL THEN 'not inspected yet' ELSE 'inspected' END As 'Another column'
    ...
    *shrugs*
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    coalesce(pass_inspection,'not inspected yet') as pass_inspection

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

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I assumed that the "yes/no" field was a boolean/bit... Would the coalesce work regardless?
    EDIT:
    Quote Originally Posted by dbenoit64
    pass_inspection ("yes"/"no")
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Code:
    SELECT *
    FROM houses h
    LEFT OUTER JOIN inspections i
    ON i.houseID = h.houseID
    AND i.inspection_date = 
    (SELECT max(inspection_date) FROM houses WHERE houseID = h.houseID)
    Rather excitingly, conditions expressed on the outer table in the join are applied BEFORE the join is applied. As such, conditions on an outer join can be used in lieu of a derived table. The same does not apply to where conditions applied on the outer table as these are applied AFTER the join is evaluated.

    Isn't that frightfully delicious?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    The same does not apply to where conditions applied on the outer table as these are applied AFTER the join is evaluated.
    which of the two tables in this example do you call the outer table?

    i stick to the terms "left" and "right" tables

    WHERE conditions on the right table in a LEFT OUTER JOIN effectively turn it into an inner join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    which of the two tables in this example do you call the outer table?

    i stick to the terms "left" and "right" tables

    WHERE conditions on the right table in a LEFT OUTER JOIN effectively turn it into an inner join
    I use inner and outer. If there were only left outer joins then no probs, but what happens if someone sneaks in and changes it to a right outer join? Why do you never hear James Tiberius Kirk ordering "hard to left Sulu"? I tell ya - it's all thought out in me noggin.

    And yes - my outer = your right in this case (as I'm sure you knew).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    ...what happens if someone sneaks in and changes it to a right outer join?
    to answer your question, with my terminology, the left table remains the left table, and the right table remains the right table

    whereas with your terminology, changing LEFT OUTER JOIN to RIGHT OUTER JOIN means that the outer table is now the inner table and the inner table is now the outer table

    whoops, i have that backwards -- the inner table is now the outer table and the outer table is now the inner table

    in both LEFT OUTER JOIN and RIGHT OUTER JOIN, using your terminology one must already need to know which is which ahead of time

    in a LEFT OUTER JOIN, for example, the results contain rows from the left table which don't have any matching rows from the right -- in effect, these left rows are outside of the right rows (matching rows would be inside), consequently in a LEFT OUTER JOIN, the outer table is actually the left table, which is completely contradictory to your terminology

    when there is so much possibility for confusion, the terminology is somewhat less than adequate

    quod erat the demo, dude
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    quod erat the demo, dude
    This is an English speaking forum Rudy - I would appreciate if you restrict your posts to the English language only

    "I went from cambridge to newcastle" is very different to "I went to newcastle from cambridge". My terminology describes the action your describes the participants. If someone told you one of those statements and asked you to meet them at their destination you wouldn't know where to go. I would.

    Anno domini nos feratu
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    If someone told you one of those statements and asked you to meet them at their destination you wouldn't know where to go.
    that's a bit of a stretch

    but your post was nevertheless quite entertaining

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

Posting Permissions

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