CODEWITHSUNDEEP
sqloracle-database
hughes
hughes

Reputation: 5713

Return row when one of many conditions is not met

I need to get a list of IDs and a corresponding field from a table where the ID may not exist. For example, the table looks like:

 id  | status
-------------
1234 | A
4567 | B
1020 | C

I want to get the status from rows with id=4567 and id=7777, like this:

Result:
 id  | status
-------------
4567 | B
7777 |

Since there is no record with id=7777 it should show an empty status field.

What I have so far: I can get an empty row when there is no record for any of the ID matches by joining the result with DUAL. For example:

SELECT id, status FROM DUAL LEFT OUTER JOIN mytable ON id='7777'

Gives the result of an empty row:

 id  | status
-------------
     |

But adding a valid id to the condition only returns one row:

SELECT id, status FROM mytable WHERE (id='7777' OR id='4567')

 id  | status
-------------
4567 | B

How can I make the query return a row with the requested ID even if it has no record?

Upvotes: 4

Views: 3892

Answers (4)

user unknown
user unknown

Reputation: 36229

With a temporary table, where you store 4567 and 7777, or an appropriate query, you could do a union:

SELECT DISTINCT n, '' 
  FROM tmp, tbl 
  WHERE n NOT IN (SELECT id FROM tbl) 
UNION (
SELECT id, status 
  FROM tmp, tbl 
  WHERE n=id);

I tested it on postgresql - it might differ from the syntax on oracle.

Upvotes: 0

Vijay
Vijay

Reputation: 67231

Use RIGHT OUTER JOIN instead of LEFT OUTER JOIN.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425391

SELECT  q.id, m.status
FROM    (
        SELECT  4567 AS id
        FROM    dual
        UNION ALL
        SELECT  7777 AS id
        FROM    dual
        ) q
LEFT JOIN
        mytable m
ON      m.id = q.id

Upvotes: 2

Nivas
Nivas

Reputation: 18334

Let outer join returns value from a table (on the left) even when there is no corresponding record in the other joined table. (right outer join is same, except that it returns data from the table on the right even if there is no corresponding record in the table on the left)

Meaning you need to have data in at least one table to get it in the result.

Actually this is the reason why you get data for 4567. You have a record corresponding to 4567 in the left table (and not in dual).

You don't have 7777 in either of the tables, and that's why it does not come in the result.

You have two options here one, as @Quassnoi has said, you can select all the records from dual, and left join with the other table.
This might not be practical if your data is dynamic or if you have a lot of data. In that case you can create a small temp table with your data and then join with the other table. To make a long story short: you need to have the data somewhere so that you join with this other table.

Upvotes: 0

Related Questions