MySQL – Writing Complex Queries Through Relational Tables

MySQL

I am generating a query that returns a set of profiles that meet all the conditions posted below

I am receiving the data like so in Ruby, then making a dynamic MySQL query based on the contents – 

[{ attribute_id: 58, parent_profile_name: 'Douglas-Connelly' },
{ attribute_id: 26, parent_profile_name: 'Brekke LLC' },
{ attribute_id: 35, val: 'Asa' },
{ attribute_id: 38, val: 'Stanton' }]

These are the current contents of the database

profile_attribute_values 
profile_id  attribute_id    parent_profile_id   val
6           58              2
6           26              5
6           35              nil                 'Asa'
6           38              nil                 'Stanton'

profile
id     name
2      Douglas-Connelly 
5      Brekke LLC
6      nil

I need to return all profiles that meet all the conditions – profiles that have a relation to profile_attribute_values, where the attribute_id is x, and the val is y, AND where the attribute_id is x, and the parent_profile name = y

WHAT I CURRENTLY HAVE 

SELECT * FROM

(SELECT P2. * 
FROM profile_attribute_values PAV
INNER JOIN profiles P1 ON P1.id = PAV.parent_profile_id
INNER JOIN profiles P2 ON P2.id = PAV.profile_id
WHERE (PAV.ne_attribute_id = '58' AND P1.`name` = 'Douglas-Connelly')
) A,

(SELECT P1. * 
FROM profiles P1
INNER JOIN profile_attribute_values PAV ON P1.id = PAV.profile_id
INNER JOIN profile_attribute_values PAV2 ON P1.id = PAV2.profile_id
WHERE (PAV.ne_attribute_id = '35' AND PAV.val = 'ASA')
AND (PAV2.ne_attribute_id = '38' AND PAV2.val = 'Stanton')
) B

WHERE A.id = B.id

This will return 

profile
id    name
6     nil

which is exactly what I want, though the tricky part is the second parent_profile condition where I need attribute_id 26, and parent_profile_name: 'Brekke LLC'

I know this wont work, but I need this to do something like this

SELECT * FROM

(SELECT P2. * 
FROM profile_attribute_values PAV
INNER JOIN profiles P1 ON P1.id = PAV.parent_profile_id
INNER JOIN profiles P2 ON P2.id = PAV.profile_id
WHERE (PAV.ne_attribute_id = '58' AND P1.`name` = 'Douglas-Connelly')
AND (PAV.ne_attribute_id = '26' AND P1.`name` = 'Brekke LLC')
) A,
.....

I am generating the SQL statement dynamically, so I really need it to be as clean as possible. I generally use ruby active record for everything, so I am a little babied when it comes to SQL statements. Thanks!

Best Answer

Looks like straightforward addition of INNER JOIN for every condition will do

SELECT id 
FROM profile p 

INNER JOIN profile_attribute_values pav1 ON p.id = pav1.profile_id
  AND pav1.attribute_id = '58' 
INNER JOIN profiles p1 ON p1.id = pav1.parent_profile_id 
  AND p1.`name` = 'Douglas-Connelly'

INNER JOIN profile_attribute_values pav2 ON p.id = pav2.profile_id
  AND pav2.attribute_id = '26' 
INNER JOIN profiles p2 ON p2.id = pav2.parent_profile_id 
  AND p2.`name` = 'Brekke LLC'

INNER JOIN profile_attribute_values pav3 ON p.id = pav3.profile_id
  AND pav3.ne_attribute_id = '35' AND pav3.val = 'ASA'

INNER JOIN profile_attribute_values pav4 ON p.id = pav4.profile_id
  AND pav4.ne_attribute_id = '38' AND pav4.val = 'Stanton'
Related Question