Jump to content

INNER JOINS, excluding records based on other tables


soccerstar_23

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.