pulling multiple entries from a match of two tables

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply