sql - I have 4 tables in MySQL - Join with 4th table without duplicates (possible null value) -

ok working 4 tables , need query return no duplicates , null value if needed. unfortunately due restriction in framework using can not use union. here tables:

users table ------------------------------------------------------ id      name ------------------------------------------------------ 1       jane 2       john 3       skip   songs table ------------------------------------------------------ id      artist      title ------------------------------------------------------ 1       devo        whip 2       snake       hiss 3       america     great 4       elvis       blues   song_history table ------------------------------------------------------ singer_id   song_id ------------------------------------------------------ 2       1 2       2 2       3 2       4   song_current table ------------------------------------------------------ singer_id   song_id     event_id ------------------------------------------------------ 2       3       20 2       4       22 

i have written 2 queries work half of results needed, here first one:

select name, artist, title users user left join song_history history on user.id = history.singer_id left join songs song on history.song_id = song.id  user.id = 2 

the above query gives me list of songs in song_history user john:

name    artist      title    ----------------------------- john  | devo      | whip    |    john  | snake     | hiss    |    john  | america   | great   |    

here second query other half of results needed:

select name, artist, title, event_id users user left join song_history history on user.id = history.singer_id left join songs song on history.song_id = song.id left join song_current csongs on csongs.singer_id = user.id , csongs.song_id = history.song_id user.id = 2 , event_id = 20 

the above query gives me list of songs in song_history in song_current table event_id of 20

name    artist      title   event_id -------------------------------------------- john  | america   | great   |   20 

now, have restriction due framework using restricts me using union lost how or if there way without union.

here results looking for:

name    artist      title   event_id ------------------------------------- john  | devo      | whip    |   null john  | snake     | hiss    |   null john  | america   | great   |   20 john  | elvis     | blues   |   null 

basically need list of songs given user in song_history table , if event_id matches needs show event_id, if not match needs show null still show songs in song_history table.

the song_history table contain in song_current (without event_id) however, song_current not contain data found in song_history.

if use union, simple use 2 successful queries above , problem solved, however, since can't use union attempts @ left inner, left outer , other joins have eluded me.

any appreciated!

edited:

note song_current table have other event_id's keep me using where/or this:

and (event_id = 20 or event_id null);

the joomla 3.3 framework included union statement , not work on multiple columns @ time.

here example of working union query works fine in phpmyadmin:

note: there additional column called users.ordering returns 0 not outlined above - using give 0 value songs did not have event_id.

select name, artist, title, users.ordering event_id users, song_history, song_current users.id = song_history.singer_id , song_history.song_id = songs.id , users.id = '2' union select name, artist, title, song_current.event_id event_id users, song_current, songs users.id = song_current.singer_id , song_current.song_id = songs.id , users.id = '2' , song_current.event_id = '20' 

keep in mind union query works 1 exception (albeit acceptable use) lists songs in song_history user 0 event_id lists songs again both in song_history , in song_current along matching event_id. result acceptable results listed above.

all need either remove filter condition on event_id or include null event_id's well, below

select name, artist, title,  case   when event_id <> 20 null   else event_id end event_id users user left join song_history history on user.id = history.singer_id left join songs song on history.song_id = song.id left join song_current csongs on csongs.singer_id = user.id , csongs.song_id = history.song_id user.id = 2 

sql fiddle demo third query includes rows null event_id.