CODEWITHSUNDEEP
sqlmysqltags
sparkle
sparkle

Reputation: 1094

SQL query for multiple tag inclusion

I´m net very good in explaining this. But i do my best:

I've made a tagging tool for people. Some people contains more then 1 tag, and i need to get those but dont know how to write the SQL query for multiple tag inclusion.

I know you can't write WHERE conditions like this:
LOWER( peoples_tags.tag_id ) = 'outside' AND
LOWER( peoples_tags.tag_id ) = 'summer'

Cause each person result is on his own row and 'outside' and 'summer' wont be on the same row. So you don't get any results by using this query.

This is the whole query:
SELECT DISTINCT peoples.*,tags.tag FROM people
JOIN peoples_tags ON peoples_tags.people_id = peoples.id
JOIN tags ON tags.tag = peoples_tags.tag_id AND
LOWER( peoples_tags.tag_id ) = 'outside' AND
LOWER( peoples_tags.tag_id ) = 'summer'

So how do i write a multiple tag SQL condition? Someone knows?
Sorry for my bad english :)

Upvotes: 5

Views: 7038

Answers (2)

Ciro Santilli OurBigBook.com
Ciro Santilli OurBigBook.com

Reputation: 384334

Just some slight improvements over https://stackoverflow.com/a/3798388/895245

SELECT
    people.*
FROM
    people
INNER JOIN
    peoples_tags
ON people.id = peoples_tags.people_id
   AND peoples_tags.tag_id IN ('outside', 'summer')
GROUP BY
    people.id
HAVING
    COUNT(peoples_tags.tag_id) = 2

namely:

  • this will work in PostgreSQL as well, HAVING Co = 2 would fail with undefined Co.
  • IN instead of the more verbose OR

See also:

Upvotes: 0

Hammerite
Hammerite

Reputation: 22350

You need to use OR instead of AND. Right now your thought process is this: "I need to get rows where the keyword is 'outside', and rows where the keyword is 'summer', so I need to use AND". But the way the RDBMS sees it, you want a given row to be returned if the keyword is 'indoors' or the keyword is 'summer'... so what you actually need is OR, not AND.

EDIT:

I see what you want to do now. Sorry for not reading more closely before.

Try this (there is probably a more efficient/scalable way of doing it, but this should work)

SELECT
    *
FROM
    people
WHERE
    EXISTS(
        SELECT
            *
        FROM
            peoples_tags
        WHERE
            peoples_tags.people_id = people.id AND
            peoples_tags.tag_id = 'outside'
    ) AND
    EXISTS(
        SELECT
            *
        FROM
            peoples_tags
        WHERE
            peoples_tags.people_id = people.id AND
            peoples_tags.tag_id = 'summer'
    )

Edit 2:

Assuming that the combination of tag_id and people_id is a unique key for peoples_tags, the following will also work:

SELECT
    people.*,
    COUNT(*) AS Co
FROM
    people
    JOIN peoples_tags ON people.id = peoples_tags.people_id
WHERE
    peoples_tags.tag_id = 'outside' OR
    peoples_tags.tag_id = 'summer'
GROUP BY
    people.id
HAVING
    Co = 2

Upvotes: 7

Related Questions