mysql - Joining two tables with sub-queries from one -

my database features multiple groups host events @ 1 or more venues. times 1 venue, groups have two. keep things simple, following data set small , imaginary:

'groups' table

+--------------+--------------+--------------+ | name         | pri_venue_id | alt_venue_id | +--------------+--------------+--------------+ | fast fingers |            3 |            0 | | data dishers |            4 |            0 | | leet hacks   |            5 |            2 | +--------------+--------------+--------------+ 

'venues' table

+----------+-------------------------+-----------------+ | venue_id | name                    | location        | +----------+-------------------------+-----------------+ |        1 | public archives         | querytown       | |        2 | storage function centre | drive bay       | |        3 | key convention centre   | qwertyville     | |        4 | head-spin mall          | drive park      | |        5 | fast storage facility   | memory bay      | +----------+-------------------------+-----------------+ 

i want join 2 tables in single query, such result of query display group, primary venue name , location, , second venue , location if there one. in case there no second/alternative venue, show primary, 2 nulls, so:

data dishers, head-spin mall, drive park, null, null fast fingers, key convention centre, qwertyville, null, null leet hacks, fast storage facility, memory bay, storage function centre, drive bay 

please note result set not have comma-delimited.

i have read other stackoverflow questions, have not yet found solution. keep getting either duplicate rows, or else alternative venue, displays null. i'm not sure best group by, saw suggested in question. tried using union, not seem work.

here closest query have come with:

select    distinct groups. name,     x. name,     x.location,     y. name,     y.location groups inner join (     select         venues.*     venues     inner join groups on venue_id = pri_venue_id ) x, groups inner join (     select         venues.*     venues     left join groups on venue_id = alt_venue_id ) y group x.venue_id order groups. name; 

thanks in advance.

one inner , 1 left join concat can trick:

select      concat_ws(', ', `name`, `name1`, location1, name2, location2) groups       (         select             g.`name`,             v1.`name` name1,             v1.location location1,             if(v2.`name` not null, v2.`name`, 'null') name2,             if(v2.location not null, v2.location, 'null') location2                     groups g         inner join venues v1 on v1.venue_id = g.pri_venue_id         left join venues v2 on v2.venue_id = g.alt_venue_id         order g.pri_venue_id     ) x 

output:

+-----------------------------------------------------------------------------------+ | groups                                                                            | +-----------------------------------------------------------------------------------+ | fast fingers, key convention centre, qwertyville, null, null                      | | data dishers, head-spin mall, drive park, null, null                              | | leet hacks, fast storage facility, memory bay, storage function centre, drive bay | +-----------------------------------------------------------------------------------+ 3 rows in set 

you can change 'null' string real null if need.