How to create mysql query result according to conditions of a condition?

Question

I want this view to be placed

ID Status 100 Viewed 103 Not Viewed 105 Viewed 

This is my sql:

 select id, status from status_table where ID in (100, 101,102,103,104,105); 

It will show the above result, because in the status table other identifiers do not have a record. ID is foreign key of another table named as table_file table. It contains in another database. So I cannot join the table due to some performance issue. So, I pass the file id as comma separated values. But I want such a result. How can I create this without using any loops.

 ID Status 100 Viewed 101 Not 102 Not 103 Viewed 104 Not 105 Viewed 

Is it possible? Please help me.

Answer 1

Is there a table in which these identifiers exist? So you can join him?

 SELECT source.ID, status.value FROM source LEFT JOIN status ON status.id = source.id WHERE source.ID in (100, 101,102,103,104,105); 

If not, you need to create a temporary table or an inline table (with these values ​​in it). Then you can simply join this table to your data.

EDIT

An example of an embedded table. There are several ways to do this, this is just one.

 SELECT source.ID, status.value FROM ( SELECT 100 AS id UNION ALL SELECT 101 AS id UNION ALL SELECT 102 AS id UNION ALL SELECT 103 AS id UNION ALL SELECT 104 AS id UNION ALL SELECT 105 AS id ) AS source LEFT JOIN status ON status.id = source.id 

Answer 2

Assuming no when there is no ID , you can do it like this:

 SELECT so.ID, CASE WHEN st.value IS NULL THEN 'Not' ELSE st.value END FROM databasename1..source so LEFT JOIN databasename2..status st ON st.id = so.id WHERE so.ID in (100, 101,102,103,104,105) 

Substitute databasename1 and databasename2 with the real database names .