I have two tables,
CREATE TABLE event (
id INT PRIMARY KEY,
CREATE TABLE event_tag (
Give me all(all columns in the table) that have associated tags with
EventTag.type="foo" and EventTag.value="bar"
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
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.