SQL: Find entries with matching criteria in different table

Question

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.

Take a look at [relational division](https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) a.k.a. "the supplier that supplies all parts". SQL/postrgre has no `divide` operator, you have to fake it with other operators, not necessarily the way @TomH suggests (but it's a good start). — onedaywhen, Jul 05 '16 at 13:54

score 2 · Accepted Answer · answered Jul 05 '16 at 13:30

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.

Thank you for reading the question carefully and even providing improvement advice on the data model. Never heard of EAV before, but I'll read up on it and redesign. — Philipp Middendorf, Jul 05 '16 at 13:33

MageeWorld · Answer 2 · 2016-07-05T13:29:55.300

0

Select id from EVENT ev INNER JOIN EVENT_TAG et ON ev.id = et.event_ID WHERE et.type = 'foo' AND et.value = 'bar'

Obviously you can put any thing you want between the parentheses to find what ever types you want.

edited Jul 05 '16 at 13:29

answered Jul 05 '16 at 13:21

MageeWorld

402
4
14

1

`bar` is a value, not a type, – onedaywhen Jul 05 '16 at 13:23
Corrected, missed that – MageeWorld Jul 05 '16 at 13:30

score 0 · Answer 3 · answered Jul 05 '16 at 13:29

Use Or operand for multiple case/criteria

 SELECT * FROM Event e join Event_tag on e.eventId = et.eventtagid where ((EventTag.type="foo" and EventTag.value="bar") or (EventTag.type="po" and EventTag.value="yo"))

or if the values is dyanmic, then depending on your programming language that interface SQL, you can write a query

For example in java I can do it using

SELECT * FROM Event e join Event_tag et on e.eventid = et.eventtagid where (EventTag.type=? and EventTag.value=?)

Where I assign the above SQL string to Query and set the parameters for it.

SQL: Find entries with matching criteria in different table

3 Answers3