CODEWITHSUNDEEP
mysqlsql
adamfowlerphoto
adamfowlerphoto

Reputation: 2751

Filtering MySQL Select based on joined table's fields

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions