MYSQL Select from tables based on multiple rows – This article will take you through the common SQL errors that you might encounter while working with php, mysql, sql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
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
Solution :
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'
Not very sure about your question. I’m assuming this is what you may want:
select * from Users where ID in (
select userID from user_meta where (meta_key = 'companyID' and meta_value = 2) or (meta_key = 'user_type' and meta_value = 'staff')
);
SELECT `users`.`id`,
`Company`.`meta_value`,
`UserType`.`meta_value`
FROM `users`
JOIN `user_meta` `Company`
ON `Company`.`userid` = `users`.`id`
JOIN `user_meta` `UserType`
ON `UserType`.`userid` = `users`.`id`
WHERE `UserType`.`meta_value` = 'staff'
AND `Company`.`meta_value` = 2
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.