0

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?

  • 2
    EAV schema pattern is bad enough, but you have made it worse by normalizing the values (eg 'ford'). – Rick James Dec 22 '18 at 22:47
  • I'm not sure what you are talking about. Are you saying the way I setup this schema is terrible? Do you propose anything better or just pointing a finger? I'd love to learn. – gstlouis May 13 '19 at 15:37
  • 1
    It's a long story. I added a tag that leads to other EAV discussions. Here's my discussion: mysql.rjweb.org/doc.php/eav – Rick James May 13 '19 at 17:55
  • Thanks for the link. I have read it and I see that this is the problem I am facing. I'm just not getting anywhere as to how I can approach this as a query. I have created another questions with better details as to what I am trying to do. I did not get much feedback yet. dba.stackexchange.com/questions/238037/…. This is my first time handling this.. any help is appreciated. – gstlouis May 14 '19 at 13:46
2

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

  • thanks I edited my query like this: 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') but it did not give me results – gstlouis Dec 22 '18 at 18:40
  • 1
    It should work using OR – McNets Dec 22 '18 at 18:48
  • 1
    If you want both (AND) then you should use EXISTS. – McNets Dec 22 '18 at 19:09
  • 1
    Have a look: dbfiddle.uk/… – McNets Dec 22 '18 at 19:11
  • 1
    OR and IN return at least one of them, if you need those results where both conditions are true use EXISTS. – McNets Dec 22 '18 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.