Ken

Ken

  • 0
  • 110
  • 0

Need help with a SQL select

Jan 4 2011 2:56 PM
Hi.  I have a wallpaper program and I recently switched it to using a database to select images from rather than the file system.  I created a table called "wr_media" to store the info on the files themselves.  I also created a table "wr_tags" to store the tag data about each picture.  (wr = WallRotate, the name of my app).  Now I have a feature called "groups" in my app where I can specify a list of tags that I want it to pull from so if I make a group titled "Muscle Cars" I can put "camaro", "mustang", "chevelle" tags in the group and when that group is chosen, it only pulls wallpaper that matches the tags.

Problem I hit is actually implementing that.  I have a "test group" button that will bring back the total # of papers matching the tag criteria.  This is the SQL I tried:

SELECT COUNT(DISTINCT filename) AS papercount FROM wr_media INNER JOIN wr_tags ON wr_media.id = wr_tags.parent WHERE tag = 'camaro' OR tag = 'mustang'

Problem is, I get the same count when I remove the mustang tag, then I realized since I said "OR" that's probably the problem.  I switched to AND, but that fails because obviously a single tag field cannot be both values at once. 

Augh!  How would I write the SQL to pull back the records that have all required tags?  Media table is indexed on "id", and tags is linked by "parent" to media's "id".  Thanks!

Answers (6)