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 protected]  |
5     |    [email protected]   |
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

Solution 1:

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'