Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

mysql query joins

  • 30-04-2011 12:09PM
    #1
    mark renton
    Closed Accounts Posts: 2,696 ✭✭✭


    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


Comments

  • #2
    mark renton
    Closed Accounts Posts: 2,696 ✭✭✭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)


  • #3
    Giblet
    Registered Users, Registered Users 2 Posts: 12,026 ✭✭✭✭Giblet


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


  • #4
    mark renton
    Closed Accounts Posts: 2,696 ✭✭✭mark renton


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


  • #5
    drag0n79
    Registered Users, Registered Users 2 Posts: 197 ✭✭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


Advertisement