CODEWITHSUNDEEP
phpmysql
Edwin Aquino
Edwin Aquino

Reputation: 1

Query to select match id and other 2 diffrent ids

I need a query that will select three different rows matching the category and one matching id with a total of 3 results.

For example, I have this table called users:

users:

user_id     user_name   category_id     
1           Sally       1                         
2           Martin      1                          
3           Mary        1                            
4           John        1
5           Phil        1 
6           Erik        1
7           Joanna      2

As you can see, all users except Joanna belong to category 1. I need a query where a total of three users are selected from category_id 1, BUT out of those three results, on of the users MUST be user_id 3.

Can this be done with just one query?

This is what I came up with, but none of these work:

SELECT user_name FROM users WHERE category_id=1 AND user_id=3 LIMIT 3

or

SELECT user_name FROM users WHERE category_id=1 AND (user_id=3 or user_id!=3) LIMIT 3

I need the query to select three users from category_id 1, and one of the three MUST be user_id 3

Thanks!

Upvotes: 0

Views: 91

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

I think taking a UNION of two queries is a good option:

SELECT user_name
FROM users
WHERE category_id=1 AND user_id=3
UNION
SELECT user_name
FROM users
WHERE category_id=1 AND user_id <> 3
LIMIT 2

The first query will return the record where user_id is 3, and the second query will retain a maximum of 2 records where category_id is also 1 but the user_id is not 3.

Upvotes: 4

Related Questions