join multiple subqueries that returns one or more rows in vertical table-CodePudding

I have large MySQL vertical table named 'profile_features' like this:

id	Profile_id	feature_id	value
1	1	1	Rick
2	1	2	Novak
3	5	3	5428
4	5	1	Joe
...	...	...	...

(above table is short part of all data)

how can I find specific Profile_ids that have ALL below conditions: (meeting the 1st AND Second And Third condition). and I want all profile_ids as result.

 profile_id FROM profile_features WHERE( feature_id IN(2, 64, 90, 38, 73, 115) AND value ='Joe')
AND
 profile_id FROM profile_features WHERE( feature_id IN(1, 55, 86, 23, 72, 114) AND value ='US')
AND
 profile_id FROM profile_features WHERE( feature_id IN(4, 59, 98, 43, 78, 120) AND value ='54782')

CodePudding user response：

Aggregate by profile and then use three separate assertions in the HAVING clause for your criteria:

SELECT profile_id
FROM profile_features
GROUP BY profile_id
HAVING SUM(feature_id IN (2, 64, 90, 38, 73, 115) AND value = 'Joe') > 0 AND
       SUM(feature_id IN (1, 55, 86, 23, 72, 114) AND value = 'US') > 0  AND
       SUM(feature_id IN (4, 59, 98, 43, 78, 120) AND value = '54782') > 0;