Geek Answers Handbook

Return a string when one of many conditions is not satisfied

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

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

I want to get the status from the lines with id=4567 and id=7777 , for example:

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

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

What I still have: I can get an empty string when there is no record for any match of identifiers, appending the result using DUAL . For instance:

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

Gives the result of an empty string:

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

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

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

How can I make a request return a string with the requested ID, even if it has no record?

+4
source share
4 answers
 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
+2
source

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

The value of the fact that you need to have data in at least one table in order to get the result.

This is actually the reason you are getting data for 4567 . You have an entry corresponding to 4567 in the left table (not dual ).

You do not have 7777 in any of the tables and why it does not come as a result.

You have two options: one, as @Quassnoi said, you can select all records from a double and left join with another table.
This may not be practical if your data is dynamic or if you have a lot of data. In this case, you can create a small temporary table with your data, and then join another table. In short: you need to have data somewhere for you to join this other table.

0
source

Use RIGHT OUTER JOIN instead of LEFT OUTER JOIN .

0
source

With a temporary table where you store 4567 and 7777 or the corresponding query, you can do a join:

 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 may differ from the oracle syntax. A.

0
source

More articles:


All Articles