I'm trying to select items from a toxi-style database. The database has the following structure:

Table "SONGS":

index	title	...
'a001'	'title1'	...
'a002'	'title2'	...

Table "TAGMAP":

index	item_index	tag_index
1	'a001'	't001'
2	'a001'	't003'
3	'a001'	't004'
4	'a002'	't003'
5	'a002'	't005'

Table "TAGS":

tag_index	name
't001'	'foo'
't002'	'bar'
't003'	'foobar'

I want to have a query, that will select items with ALL desired tags present. Using this article, I've built the following query, that works:

SELECT s.title, s.description
FROM (songs s, tagmap tm)
WHERE (tm.tag_index IN (7,3))
AND s.index = tm.item_index
GROUP BY s.item_index 
HAVING COUNT(s.item_index) = 2 
ORDER BY s.date DESC

The IN (7,3) array is dynamically populated in practice, with COUNT(...) = <x> being the length of the desired tags array. This query works.

However, now I also need the names of all associated tags returned with every item. Previously (a query of all items, without respecting any tags), this worked by using joins. But now, when introducing the same JOIN I used previously to this query, it falls apart. The result isn't the same, it's less items and they are even completely different!

If tag2 and tag2 are the tags I'm interested in, the expected output would be:

Title	Description	...	tags
title1	something	...	tag1, tag2, tag3 ...
title2	otherthing	...	tag2, tag3, tag5 ...
...	...	...	...

The following record would not be included because one of the required tags (in the specific case, "tag3") is missing:

( 'title5' , 'somewhere' , ... , 'tag1, tag2, tag5 ...' )

Here's my attempt at this task:

SELECT s.title, s.description, GROUP_CONCAT(t2.name SEPARATOR ', ') as tags
FROM (songs s, tagmap tm)
LEFT JOIN tagmap tm2 ON s.index = tm2.item_index 
LEFT JOIN tags t2 ON tm2.tag_index = t2.tag_index
WHERE (tm.tag_index IN (7,3))
AND s.index = tm.item_index
GROUP BY s.item_index 
HAVING COUNT(s.item_index)=2 
ORDER BY s.date DESC

I fail to see where this is broken so any help will be highly appreciated!

CodePudding user response：

Remove the filtering and use conditional aggregation to check if your tags are present among all the tags.

SELECT s.title, s.description, GROUP_CONCAT(t.tag_name) AS tags
FROM      songs s
LEFT JOIN tagmap tm
       ON s.index = tm.item_index
LEFT JOIN tags t 
       ON tm.tag_index = t.tag_index
GROUP BY s.title, s.description
HAVING COUNT(CASE WHEN t.tag_index IN (2,3) THEN 1 END) = 2

Note that this solution assumes you don't have duplicated tags. If you can have a title associated multiple times to a single tag, you should split the conditions in the HAVING clause as follows:

HAVING MAX(tag_index=2) = 1 
   AND MAX(tag_index=3) = 1

This ensures that there's at least one true value (the respective tag exists).

CodePudding user response：

There is no need to for a second bridge table,.

also don't mix comma separated join with other joins, as comma separated are old style anyway

so basically

SELECT MAX(s.title) as tiltle, MAX(s.description) as description, GROUP_CONCAT(t2.name SEPARATOR ', ') as tags
FROM songs s  INNER JOIN tagmap tm ON s.index = tm.item_index
INNER JOIN   tags t2 ON tm.tag_index = t2.tag_index
WHERE (tm.tag_index IN (7,3))
GROUP BY s.item_index 
HAVING COUNT(s.item_index)=2 
ORDER BY s.date DESC

to get all the tags you need to get all item.index that have those tags

SELECT MAX(s.title) as tiltle
 #, MAX(s.description) as description
  , GROUP_CONCAT(t2.name SEPARATOR ', ') as tags
FROM SONGS s  INNER JOIN TAGMAP tm ON s.index = tm.item_index
INNER JOIN   TAGS t2 ON tm.tag_index = t2.tag_index
WHERE s.index IN ( SELECT item_index FROM TAGMAP WHERE tag_index IN ('t003','t004'))
GROUP BY s.index 
HAVING COUNT(tm.item_index)=3 
#ORDER BY s.date DESC

tiltle	tags
title1	foo, foobar, foobar3

fiddle

If both have to be at the same time tag for the item you can do following

SELECT MAX(s.title) as tiltle
 #, MAX(s.description) as description
  , GROUP_CONCAT(t2.name SEPARATOR ', ') as tags
  
FROM SONGS s  INNER JOIN TAGMAP tm ON s.index = tm.item_index
INNER JOIN   TAGS t2 ON tm.tag_index = t2.tag_index
GROUP BY s.index 
having SUM( (tm.tag_index = 't003')   (tm.tag_index = 't004'))  = 2
#ORDER BY s.date DESC

tiltle	tags
title1	foo, foobar, foobar3

fiddle