Need help with a SQL select

Question

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!

Ken · Answer

One of the DBAs at my office came up with something: select count(filename) from wr_tags inner join wr_media on wr_tags.parent = wr_media.id where parent in ( select parent from wr_tags where tag = 'camaro' ) and tag = 'tubbed' Thanks for the help. Figured I'd post this in here in case any one else goes looking for the same kind of thing. It was slow as hell until I indexed tag now it flies.

theLizard · Answer

That being the case there is nothing you can do without introducing a new field to accommodate your needs to make it simple however you could try where tag = 'tubbed' OR tag = 'camaro' AND tag != 'mustang' but this would need to be done for every description you do not want. you would be better to do something like select count(filename) as papercount FROM wr_media INNER JOIN ...ONB ... WHERE wr_tags.parent = 109 AND tag = 'camaro' OR tag = 'tubbed'

Ken · Answer

Hmm. I may not be explaining it right.. I need all records where a given parent id has both tags. If my wr_tags has the following data: index: 1 parent: 109 tag: camaro index: 2 parent: 109 tag: red index: 3 parent: 110 tag: mustang index: 4 parent: 110 tag: tubbed index: 5 parent: 111 tag: camaro index: 6 parent: 111 tag: tubbed index: 7 parent: 112 tag: mustang If I use OR I get back records 1 4 5 and 6. However record 4 belongs to a parent whose tags are mustang tubbed so that doesn't match my desired filter of papers that are both camaro and tubbed.

theLizard · Answer

You need to use the OR operator tag = 'camaro' OR tag = 'tubbed' will give you all records with camero and tubbed

Ken · Answer

Thanks. That works dunno why I had put that distinct in there. One last question.. how would I do it if the group is supposed to match all tags and not just any? Like I want to get files that are tagged camaro and tubbed. I tried tag = 'camaro' and tag = 'tubbed' but it didn't like that since no singular record can be both. Sorry for my ineptitude thanks for the help.

theLizard · Answer

my guess is try getting rid of distinct the OR part should not be giving you the problem.