Reputation: 2751
I have two tables. Created as follows.
CREATE TABLE item (
id INT AUTO_INCREMENT,
value VARCHAR(64),
PRIMARY KEY(id)
)
CREATE TABLE tag (
name VARCHAR(32),
item_id INT /* id of element in item table */
)
I have a select statement that returns a list of elements in the 'item' table along with all the elements of the 'tag' table linking to that table. It is filtered on the contents of the item.value field.
SELECT id,value,GROUP_CONCAT(tag.name) FROM item
LEFT JOIN tag ON tag.item_id = id
WHERE value LIKE '%test%'
All good so far. Now I want to do the same but get a list of all the item table elements with a certain tag associated with it. So I replace the WHERE query with
WHERE tag.name='test'
This gives me a list of all the 'item' elements which have the tag 'test' but the grouped tag list that come along with it only includes the tag 'test'.
How do I get a list of all the elements of the table 'item' which have tag 'test' along with the full group tag list?
Upvotes: 0
Views: 70
Reputation: 1271231
First, you should have a GROUP BY
in your original query:
SELECT i.id, i.value, GROUP_CONCAT(tag.name)
FROM item i LEFT JOIN
tag t
ON t.item_id = i.id
WHERE i.value LIKE '%test%'
GROUP BY i.id, i.value
To get only rows that have a certain tag, add:
HAVING SUM(t.name = 'test') > 0
after the GROUP BY
.
Upvotes: 1