How create mysql query result according to where condition values?

Question

I want this kind of an out put

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 status table other ids don't have any entry. 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 am passing file ID as comma separated values. But I want this kind of result How can I create this with out 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

Do you have a table where those IDs DO exist? So that you can join on it?

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 inline table (with those values in it). Then you can just join that table to your data.

EDIT

Example of an inline 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 'Not' when the ID is missing, 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 to the real database names.