mysql query joins

mark renton

I have a code below - works fine - i would like to add reserve table, which is joined to stock table (stock_id) - i need only data in reserve table if there is a stock_id in reserve table - so i guess i need left join stock table with reserve table

this being god awful mysql joins (cant believe there wasnt a less complex coding way to do this) i cant seem to get it to work

if it was oracle sql (god bless oracle) i would have one line to add
stock.stock_id=reserve.stock_id(+) and hey presto

can someone make this less painful for me please??

select a.first_name, a.last_name, b.type, c.*, e.status
from person a, person_group b, item_transaction c, stock d, status e
where a.group_id=b.group_id 
and a.person_id=c.person_id
and c.stock_id=d.stock_id
and d.status_id=e.status_id

mark renton

i got it an easier way as below, but if anyone could post the mysql join query for code above i would appreciate it

select a.first_name, a.last_name, b.type, c.*, e.status, f.reserve_id 
from person a, person_group b, item_transaction c, stock d, status e, reserve f
where a.group_id=b.group_id 
and a.person_id=c.person_id
and c.stock_id=d.stock_id
and d.status_id=e.status_id
and c.stock_id=f.stock_id
UNION ALL
select a.first_name, a.last_name, b.type, c.*, e.status, if(e.status < 1000, 'NORESERVE', 'changequery') as reserve_id
from person a, person_group b, item_transaction c, stock d, status e
where a.group_id=b.group_id 
and a.person_id=c.person_id
and c.stock_id=d.stock_id
and d.status_id=e.status_id
and c.stock_id not in (select stock_id from reserve)
 

Giblet

select * from stock s left join reserve r on s.stock_id = r.stock_id

mark renton

yes i can do it with 2 tables but cannot do it with multiple tables

drag0n79

select a.first_name, a.last_name, b.type, c.*, e.status, f.reserve_id
from
person a
join person_group b on a.group_id=b.group_id
join item_transaction c on a.person_id=c.person_id
Join stock d on c.stock_id=d.stock_id
Join status e on d.status_id=e.status_id
Left Join reserve f on d.stock_id=f.stock_id