Jump to content


Photo

INNER JOINS, excluding records based on other tables


  • Please log in to reply
3 replies to this topic

#1 soccerstar_23

soccerstar_23
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 18 September 2006 - 02:44 AM

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!


#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 18 September 2006 - 07:27 AM

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)"

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 September 2006 - 05:28 PM

Looks good... I'm confused about the != condition.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 soccerstar_23

soccerstar_23
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 19 September 2006 - 03:57 AM

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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users