Re: Delicious style Tags table

> Evert Lammerts-2 wrote: >> >> Your 'taggings' table is basically a link table to deal with a 'many >> to many' relationship - i guess you know: >> >> You have a table A and a table B that have a many-to-many >> relationship, which is stored in table L. What query fetches all rows >> in A that relate to all members of the set [B1, B2, ..., Bn]? >> > > Well I was planning on getting the tags back first because in theory table B > could be C or D or whatever. I handle that in code by processing all the > records I get back from the query to find out what table that are in. ??? Don't understand... >> You can manage to do this without the joins, I think, by using a >> nested query and the HAVING keyword in your SQL. Selecting all ids of >> entries that apply to all tags from a linktable would go something >> like this: >> >> SELECT link.entry_id, COUNT(link.entry_id) AS amount_of_links FROM >> link WHERE link.tag_id IN (SELECT id FROM tag WHERE tag.name='soup' OR >> tag.name='vegetarian') GROUP BY link.entry_id HAVING amount_of_links >>>= 2 >> > > You know this got me thinking and I came up with this > > SELECT tags.*, count(taggable_id) as count, > taggings.* FROM tags > JOIN taggings ON tags.id = taggings.tag_id WHERE (name IN > ('vegetarian', > 'soup')) group by taggable_id > > Now that gives me ssomething I can work with 'count' should be the same > value as the number of tags supplied (2), but I couldn't figure out how to > add that into the where clause, of course I could use a subselect and just 1 > will be fine, something like > > <pre> > SELECT * from > (SELECT tags.name, count(taggable_id) as count, > taggings.* FROM tags > JOIN taggings ON tags.id = taggings.tag_id WHERE (name IN > ('vegetarian', > 'soup')) group by taggable_id ) tbl1 > WHERE cnt = 2 > </pre> This is what the HAVING keyword does. I'm never happy about using it though. I've sent your question on to an expert - It's bugging me :-) Will hopefully be back with an answer soon! Evert