Complex MySQL query through relational tables

Question

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!

Answer 1

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'

Answer 2

@Serg's answer is effective and fast for a small set of attributes to compare, but it is limited to 30, i.e. hitting the maximum 61 tables that can be referenced in a query (MySQL 5.x).

The following is another equivalent but arguably more costly plan, however this one is not limited in the number of attribute tests (you would build dynamically the must table of UNION ALL rows, and leave the rest of the query as is):

SELECT *
FROM `profiles`
WHERE id IN (
    SELECT p.id
    FROM (
        SELECT 58 AS attribute_id, 'Douglas-Connelly' AS parent_profile_name, NULL AS val
        UNION ALL SELECT 26, 'Brekke LLC', NULL
        UNION ALL SELECT 35, NULL, 'Asa'
        UNION ALL SELECT 38, NULL, 'Stanton'
        ) must
    CROSS JOIN `profiles` p
    LEFT JOIN `profiles` pp ON (pp.name = must.parent_profile_name)
    LEFT JOIN profile_attribute_values a ON 
                   (a.profile_id = p.id AND
                    a.attribute_id = must.attribute_id AND
                    (a.parent_profile_id = pp.id OR must.val IS NOT NULL AND a.val = must.val))
    GROUP BY p.id
    HAVING SUM(CASE WHEN a.attribute_id IS NULL THEN 1 ELSE 0 END) = 0
  )