pulling multiple entries from a match of two tables

Question

I have two tables, attributes and attributes_entries

attributes hold the title and attribute_entries hold the value of that title.

if I do:

SELECT attribute_entries.title AS ent_title, 
       attributes.title AS attr_title 
FROM   attribute_entries 
INNER JOIN attributes 
       ON attribute_entries.FK_attribute_id = attributes.id 
WHERE attribute_entries.title = 'ford' 
      AND attributes.title = 'manufacturer'

I can get a result of:

ent_title | attr_title
ford      | manufacturer

if I do another query of:

SELECT attribute_entries.title AS ent_title, 
       attributes.title AS attr_title 
FROM   attribute_entries 
INNER JOIN attributes 
      ON attribute_entries.FK_attribute_id = attributes.id 
WHERE attribute_entries.title = 'whatev' 
      AND attributes.title = 'nickname'

I get the result of:

ent_title | attr_title
whatev    | nickname
whatev    | nickname

both of these queries allign with the data I have in there.

so I figured this would work:

SELECT attribute_entries.title AS ent_title, 
       attributes.title AS attr_title 
FROM attribute_entries 
INNER JOIN attributes 
      ON attribute_entries.FK_attribute_id = attributes.id 
WHERE attribute_entries.title = 'ford' 
      AND attributes.title = 'manufacturer' 
      AND attribute_entries.title = 'whatev' 
      AND attributes.title = 'nickname'

This clearly isn't working. What is my best approach to get the results in one statement?

Answer 1

You should filter using pairs:

WHERE (attribute_entries.title = 'ford' AND attributes.title = 'manufacturer')
      OR
      (attribute_entries.title = 'whatev' AND attributes.title = 'nickname')

Or you can use IN

WHERE attribute_entries.title IN ('ford', 'whatev')
      AND attributes.title IN ('manufacturer', 'nickname')

In case you need both conditions true you can use EXISTS:

WHERE EXISTS(<your first query>)
      AND
      ExISTS(<your second query>)

You can check it :db<>fiddle here