Jump to content

Archived

This topic is now archived and is closed to further replies.

soccerstar_23

INNER JOINS, excluding records based on other tables

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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)"

Share this post


Link to post
Share on other sites
Looks good... I'm confused about the != condition.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.