sql - mysql outer join - returning a complete list of records that don't always have entries in a correlated table -
this in mysql v. 4.x... i'm sure rookie question reason can't figure out sql work. here approximation of relevant tables:
item --- itemid
etc.
itemperson -------- personid itemid timeaccessed
let's have item id's of 1,2,3,4,5
let's have 1 record in itemperson: {'johndoe',1,'12:00pm'}
i have personname input. want returned list of items, including time item accessed. if item not accessed given personname, want null time.
i've tried following:
select i.*, ip.timeaccessed item left outer join itemperson ip on i.itemid = ip.itemid (ip.personname = 'johndoe' or ip.personname null)
i expected results 'johndoe'... items returned , itemid 1 has time.
if change 'johndoe' 'janedoe', itemid's 2-5 want query return items, null times.
left joins can tricky in these circumstances. you'll want move person condition on clause; otherwise getting items associated person or no 1 @ all. (logically speaking, optimizer aside, occurs after join).
Comments
Post a Comment