> 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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
