Filter data by multiple rows in second table MYSQL

I have 2 tables. users and users_meta. I want the query to return all users that have sexe=M and user_att1=1 (which should return only mathew) but I also need to be able to filter by other attributes as well.

users table is organized as such:

ID  | Firstname | Lastname  | Sexe     etc.
1      Mathew      Perkins     M
2      Andrea      Smith       F
3      Andrew      Story       M

users_meta table is organized as such:

ID  | user_id |  meta_key   | meta_value 
1   | 1       | user_att1   | 1
2   | 1       | user_att2   | 0
3   | 2       | user_att1   | 0
4   | 2       | user_att2   | 1
5   | 3       | user_att1   | 0
6   | 3       | user_att2   | 1

I tried using group_concat and concat and could not get the desired result.

This is my current query, which gives the desired output but I am not filtering by the attribute if that makes sense.

SELECT 
    users.id,users.firstname,
    users.lastname, 
    concat(users_meta.meta_value) as 'user_att1' 
FROM users 
LEFT JOIN users_meta ON users.id=users_meta.user_id 
WHERE users_meta.meta_key='user_att1' 
GROUP BY users.id 
ORDER BY ID DESC 
LIMIT 0, 20

Thanks for any help or pointing me in the right direction

Answer

Given your use case, I would use conditional aggregation:

SELECT u.id, u.firstname, u.lastname
FROM users u
INNER JOIN users_meta um ON um.user_id = u.id 
WHERE u.sexe = 'M'
GROUP BY u.id, u.firstname, u.lastname
HAVING MAX(um.meta_key = 'user_att1' AND um.meta_value = 1) > 0
ORDER BY u.id DESC
LIMIT 0, 20

The nice thing about this technique is that you can add more criteria on users_meta.meta_key simply by adding more conditions in the HAVING clause.

Advertisement

Answer