Philipp Middendorf Philipp Middendorf - 5 months ago 8
SQL Question

SQL: Find entries with matching criteria in different table

I have two tables,

Event
and
EventTag


CREATE TABLE event (
id INT PRIMARY KEY,
content TEXT
)

CREATE TABLE event_tag (
event_id INT,
type VARCHAR(255),
value VARCHAR(255)
)


Each event has zero or more tags. The query I'd like to express in SQL is:


Give me all
Event
(all columns in the table) that have associated tags with
EventTag.type="foo" and EventTag.value="bar"
.


This is easy for one tag criterion (for example, with a join and a where, as answered here), but how do I tackle the situation of two or more criteria? So: Give me the events that have an associated tag "foo" equal to "bar" and (!) an event tag "qux" equal to "quux"? I thought about joining the tag table 'n' times, but I'm not sure if it's a good idea.

Answer

The best way to solve this problem is to not use the EAV database model (Entity-Attribute-Value). You're running into just the first of many problems with this anti-pattern. A quick Google search on "EAV model" should reveal some of the other problems in store for you if you choose not to redesign. Normally your Event table should have a column for foo and a column for qux.

One possible solution that you can use, if you insist (or are forced) to go down this path:

SELECT id, content
FROM Event
WHERE id IN
(
    SELECT
        E.id
    FROM
        Event E
    INNER JOIN Event_Tag T ON
        T.event_id = E.id AND
        (
            (T.type = 'foo' AND T.value = 'bar') OR
            (T.type = 'qux' AND T.value = 'quux')
        )
    GROUP BY
        E.id
    HAVING
        COUNT(*) = 2
)

If you put your various type/value pairs into a temporary table or as a CTE then you can JOIN to that instead of listing out all of the pairs that you want. That syntax will be dependent on your RDBMS though.