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

    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 Inner Join
    florence.rel_flo_roo On florence.rel_flo_roo.ROO_ID =
    Inner Join
    florence.floor On florence.rel_flo_roo.FLO_ID = florence.floor.FLO_ID
    Left Join
    On = florence.fl_opname_defect.ROO_ID Inner Join
    florence.fl_uptake On florence.fl_opname_defect.Completed =
    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.

    Attached Thumbnails Attached Thumbnails ScreenShot177.bmp  

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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 | @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