MYSQL Select from tables based on multiple rows

Question

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

provide the table structure of both this tables and also the desired result came from your example datas — Beginner
– Beginner, Commented Oct 28, 2016 at 1:55
@NewbeeDev I added the users table, the only important column is ID. users.ID = user_meta.userID — Dylan Cross
– Dylan Cross, Commented Oct 28, 2016 at 1:58
perhaps a result your desired query would help us understand your problem more. — Beginner
– Beginner, Commented Oct 28, 2016 at 2:04

Barmar · Accepted Answer · 2016-10-28 02:20:24Z

5

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'

answered Oct 28, 2016 at 2:20

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dylan Cross Over a year ago

This seems to be working how I wanted.. I will finish coding to make sure. Thank you!

Beginner · Accepted Answer · 2016-10-28 02:53:34Z

0

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

https://gyazo.com/de8d9124418f65b993d708c80c309325

edited Oct 28, 2016 at 2:53

answered Oct 28, 2016 at 2:09

Beginner

4,1183 gold badges19 silver badges27 bronze badges

Comments

Prem Raj · Accepted Answer · 2016-10-28 02:16:38Z

-1

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')
);

answered Oct 28, 2016 at 2:16

Prem Raj

8475 silver badges15 bronze badges

4 Comments

Barmar Over a year ago

This will get everyone who is either in company 2 or is staff, it doesn't require them to be both.

Prem Raj Over a year ago

@Barmar Ah yes you are right. I didn't get the question right

Dylan Cross Over a year ago

@PremRaj yes thank you for your attempt, I know the question is a bit confusing.

Barmar Over a year ago

It's actually a pretty common request, and wrong answers like this are also common.

Collectives™ on Stack Overflow

MYSQL Select from tables based on multiple rows

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related