INNER JOINS, excluding records based on other tables

[soccerstar_23]

Hello,

I'm creating a photo gallery and for individual photos, you may "tag" one.  For example, when someone makes a stupid face in the photo, you may give the photo the "Funny" tag.  Each photo may have multiple tags (e.g. "Funny", "Stupid", "Idiot"). 

To let users add a tag to a photo, I give them a "<SELECT>" box to choose from a list of existing tags.  Obviously, if the photo already has the "Stupid" tag, I do not want tag showing up in the dropdown list.

I'm having problems coming up with a good MySQL statement to get the information.  The tables are as follows:

MEDIA
--------------
media_id
media_title
......

MEDIA TAG
--------------
media_id
tag_id
user_id

USERS
--------------
user_id
user_handle
....

Seeing as tags can be used on multiple photos, the JOIN seems to be pretty complicated.

I've tried a variation of the following with incorrect results:

SELECT * FROM dg_tags t
LEFT JOIN (dg_media_tags mt)
ON (t.tag_id = mt.tag_id AND mt.media_id != 6)
ORDER BY t.tag_title

What can I do to get the select box of "tags" that arent' in use for a specific photo?

Thank you!

[btherl]

SELECT * FROM dg_tags t
LEFT JOIN (dg_media_tags mt)
ON (t.tag_id = mt.tag_id AND mt.media_id != 6)
WHERE mt.tag_id IS NULL
ORDER BY t.tag_title

How about this?  The logic is that the left join will put NULL for every column in mt where there is no corresponding tag in t.  So the "IS NULL" condition will give you only these.

If you can't get it working, use a subquery which selects all the currently set tags, and specify "where tag_id not in (subquery)"

[fenway]

Looks good... I'm confused about the != condition.

[soccerstar_23]

Thanks for your help.  I had to use a subquery to get this to work but again, thanks for your help!