CODEWITHSUNDEEP
mysql
Satish Saini
Satish Saini

Reputation: 2968

MYSQL IN Clause Issue

There are a lot of questions posted on stackoverflow that are almost same like my problem but I found no working solution. I have a table message and entries are:

id | Message | Status
1  |  Hello  |  1
2  |  Hiiii  |  0
4  |  Works  |  1

I have another table which gives ids 1,2,3 and I want to find the status of all these entries from message table. What I want is if an id doesn't exist in message table then it should return null for that id if I use IN clause to find all status. I want following result:

id | Status
1  | 1
2  | 0
3  | null

I have been using IN clause but didn't get working output. Then I got a solution on stackoverflow and tried this query

SELECT `id`,`status` FROM ( SELECT 1 AS ID UNION ALL SELECT 2 AS ID UNION ALL SELECT 3) ids LEFT OUTER JOIN message ON ids.ID = message.id

But this query is not giving the expected results. Any help would be much appreciated.

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I don't see how your query would work. The column id should be ambiguous in the select (unless your database is case sensitive). Try this:

SELECT ids.ID, m.status
FROM ( SELECT 1 AS ID UNION ALL SELECT 2 AS ID UNION ALL SELECT 3
     ) ids LEFT OUTER JOIN
     message m
     ON ids.ID = m.id;

Upvotes: 2

Related Questions