three tables select from string

[j milo taylor]

I'm a bit stuck with this. I need rows returned from a string from three tables. As is, this returns only those rows which have id_work = workid_audio. I want all the rows, that match

id_year = %s
AND year_work = year_year
AND workID_artist = artistID_work

and to pull in the relevent file_audio for those records that have this

Here's my select ////

SELECT
id_year,
year_year,

id_work,
name_work,
location_work,
worktype_work,

2ndname_artist,

workid_audio,
file_audio

FROM year, work, artist, audio

WHERE

id_year = %s
AND year_work = year_year
AND workID_artist = artistID_work
AND id_work = workid_audio <---- but how to also return rows which have no audio)

ORDER by 2ndname_artist

Can anyone help?
Thanks
Milo

[syber]

You need a LEFT OUTER JOIN to return rows that have no audio:

FROM year INNER JOIN work 
 ON year_work = year_year 
INNER JOIN artist 
 ON workID_artist = artist_ID_work 
LEFT OUTER JOIN audio 
 ON id_work = workid_audio 
WHERE id_year = %s 

[j milo taylor]

Dear Syber,

Thankyou so much, that works perfectly. I've been battling with this for a few months.

Now I will be able to sleep at night!

Thanks again,

Best Regards
Milo