Searching one to many database using where clause

Question

I'm trying to construct a query that will allow me to pull a person with the defined attributes.

+----------------------------------------------------+
TABLE: Person
+----------------------------------------------------+
owner_id | name
1        | kevin
2        | lee

+----------------------------------------------------+
TABLE: Attributes
+----------------------------------------------------+
id              | owner_id       | attributes_id
1               | 1              | 52
2               | 1              | 53
3               | 1              | 23
4               | 2              | 52


SELECT Person.name FROM Person LEFT JOIN `Attributes` ON `Attributes`.`owner_id` = `Person`.`owner_id` WHERE Attributes.attributes_id = 52 AND Attributes.attributes_id = 53;

Using that where clause didn't return the owner_id 1. If somebody could point me in the right direction I would be most greatful!

Answer 1

SELECT Person.name 
FROM Person 
JOIN `Attributes` A1 ON A1.`owner_id` = `Person`.`owner_id` 
JOIN `Attributes` A2 ON A2.`owner_id` = `Person`.`owner_id` 
WHERE A1.attributes_id = 52 AND A2.attributes_id = 53; 

I am presuming you want someone with all the attributes you list. I changed your left join to an inner join because it was that effectively anyway. You would have to join to the atrributes table separately for each attribute you require.

Another way is :

SELECT Person.name 
FROM Person 
JOIN `Attributes`  ON `Attributes`.`owner_id` = `Person`.`owner_id` 
WHERE `Attributes`.attributes_id = 52 OR `Attributes`.attributes_id = 53
GROUP BY Person.name 
Having count(*) = 2;