Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    1

    Thumbs down Unanswered: Problems in query building

    I am not an SQL expert and have difficulties building a complicated query.
    I use the tool flyspeed query to design the query visually.
    In that tool the query works and returns records from mysql when I run the same query on MySQL I got zero records.

    This is the query

    Select * From
    florence.room Inner Join
    florence.rel_flo_roo On florence.rel_flo_roo.ROO_ID = florence.room.ROO_ID
    Inner Join
    florence.floor On florence.rel_flo_roo.FLO_ID = florence.floor.FLO_ID
    Left Join
    florence.fl_opname_defect
    On florence.room.ROO_ID = florence.fl_opname_defect.ROO_ID Inner Join
    florence.fl_uptake On florence.fl_opname_defect.Completed =
    florence.fl_uptake.UPT_ID
    Where
    florence.fl_opname_defect.Completed = 0

    In the attachment you see the query I have made so far. I want to get all the rooms on a floor and see if ther are defects/maintenance items for that floor. So all the rooms on a floor and some of them has maintenance items.

    John
    Attached Thumbnails Attached Thumbnails ScreenShot177.bmp  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you use LEFT JOIN to attach fl_opname_defect rows to the intermediate rows being built by the FROM clause

    this allows for the possibility that some rooms don't have defects, so the columns in the intermediate rows that would come from fl_opname_defect will be set to NULL

    (that's not news, that's the way left joins work)

    but then you INNER JOIN fl_uptake rows based on a match between fl_uptake and fl_opname_defect

    that means any intermediate rows where fl_opname_defect.Completed is NULL because fl_opname_defect was missing, will be discarded, because NULL is not equal to any value that fl_uptake.UPT_ID might have

    change this last join to LEFT JOIN as well
    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
  •