Dylan Cross Dylan Cross - 1 month ago 11
MySQL Question

MYSQL Select from tables based on multiple rows

I have a table called

user_meta
. In that table I have the following columns:
ID
,
userID
,
meta_key
,
meta_value


I have another table called
users
, the only important column there is
ID
, which I want to compare to the
user_meta
table rows.

The
users
table looks like:

ID | email | etc...
1 | email@test.com |
5 | testa@a.com |
6 | .... |
7 | .... |


So say I have a table (user_meta) that looks like:

ID | userID | meta_key | meta_value
2 | 1 | companyID | 2
3 | 1 | user_type | staff
4 | 5 | companyID | 2
5 | 5 | user_type | staff
6 | 6 | companyID | 4
7 | 6 | user_type | customer


I want to retrieve a single row for each
userID
, but only if the company ID and user_type are correct.

I want to retrieve all users that have the same companyID that I would send in the query, so let's say $companyID=2, and then all users that have the
user_type
='staff'.

So user_meta.userID must equal users.ID, and user_meta.companyID must equal 2, and user_meta.user_type must equal 'staff'.

I want a list of all users that match these criteria.

A result would be
userID
1 & 5 are returned. They both have
companyID = 2
, and both have
user_type = staff

Answer

You need to join with user_meta once for each attribute you want to match.

SELECT u.*
FROM users AS u
JOIN user_meta AS m1 ON u.id = m1.userID
JOIN user_meta AS m2 ON u.id = m2.userID
WHERE m1.meta_key = 'companyID' AND m1.meta_value = :companyID
AND m2.meta_key = 'user_type' AND m2.meta_value = 'staff'