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:
x
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
Advertisement
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.