CODEWITHSUNDEEP
sqlmultiple-tables
Philipp Middendorf
Philipp Middendorf

Reputation: 353

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.

Upvotes: 0

Views: 757

Answers (3)

Tom H
Tom H

Reputation: 47464

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.

Upvotes: 2

MageeWorld
MageeWorld

Reputation: 402

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.

Upvotes: 0

Raj K
Raj K

Reputation: 470

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.

Upvotes: 0

Related Questions