outer joins with criteria

[smook]

i've got 3 tables - document, section and item

sections are default across all documents, items are specific to the section and the document... when i first make the document then, i wanted query out of the item and section tables - ALL the sections, and only the items that belong to the document.... so i thought:

select doc_id, section_id,item_id from sections left joins items on sections.section_id = items.item_id where items.doc_id = {given doc_id} OR items.doc_id is Null

so when the items table was empty and there were no items matched with any section - then sections with - items appearred because the doc_id was null.... (say doc_id 1) - then i filled up the items and it still all works....

however after i completed one document with all the sections... then query ... i get nothing... no sections... because i'm now querying for all sections (and items) and that actually matches doc_id 1... but when i narrow it down to doc_id 2 ... they all disappear... doc_id 2 <> doc_id 1 and none of them are null.

i know this has to be obvious but i'm just not seeing it... how do i query out these items with all sections? (btw i'm using mysql 3.23.56 if it makes a difference)

sm

😕

[smook]

of course... i remembered after playing with this for a bit. In case anyone else is looking for the same thing - you can put more than one criteria in your ON clause so where i had:

select doc_id, section_id,item_id from sections left joins items on sections.section_id = items.item_id where items.doc_id = {given doc_id} OR items.doc_id is Null

change it to:

select doc_id, section_id,item_id from sections left joins items on sections.section_id = items.item_id and items.doc_id = {given doc_id}

this left joins the sections with only the items you were looking for - when there are no items for sections... it includes them with nulls.

simple -
sm

😃