SQL: How to return an non-existing row?

Question

Say I have the following table:

=================================================
| color_id | parent_id | language_id | name     |
=================================================
| 1        | 50        | 1           | Black    |
-------------------------------------------------

Then say I need the row WHERE parent_id = 50 AND language_id = 2. Obviously, I would get nothing back based on my example table. However, I still need a result -- probably something like this:

=================================================
| color_id | parent_id | language_id | name     |
=================================================
| NULL     | 50        | 2           | NULL     |
-------------------------------------------------

Is there a way to do this in SQL?

Out of curiosity, why do this? It's an interesting question, but I'm hard pressed to think of a reason for it. — David, May 08 '11 at 03:15
@David: OMG Ponies is right. I'm trying to use COALESCE to do language translations (say, if `name` exist for a particular language, then use it; otherwise use the first non-NULL `name` that exist). Problem is when the row for a particular language does not exist, then I can't seem to use COALESCE. If I get a row with the NULL values, then I can use my COALESCE solution. — StackOverflowNewbie, May 08 '11 at 03:56

MPelletier · Accepted Answer · 2011-05-08T04:06:08.557

7

You could do a union query of both the potentially valid record and your default, then select the first one:

SELECT * FROM
(SELECT color_id, parent_id, language_id, name, 1 as order_rank
 FROM some_table
 WHERE parent_id = %parent_id% AND language_id = %language_id%
 UNION
 SELECT NULL, %parent_id%, %language_id%, NULL, 2 as order_rank
)
ORDER BY order_rank
LIMIT 1

(Edited with static value for ordering as suggested by OMG Ponies)

edited May 08 '11 at 04:06

answered May 08 '11 at 03:10

MPelletier

16,256
15
86
137

I'm certain there's something with either IF or CASE, but MySQL syntax isn't the flavour I'm normally used to. – MPelletier May 08 '11 at 03:16
+1: Only thing I'd add would be a statically defined value for ordering by, to ensure the proper row is returned when the value searched for exists. – OMG Ponies May 08 '11 at 03:19
Agreed, a very nice solution. – mesch May 08 '11 at 03:42
@OMG Ponies, can you please elaborate on what you mean by adding "a statically defined value for ordering by"? Thanks! – StackOverflowNewbie May 08 '11 at 03:57
1

@StackOverflowNewbie: I believe he meant it as I put up in the latest edit. It enforces ranking, that's for sure. – MPelletier May 08 '11 at 04:07
@StackOverflowNewbie: ^ what he said – OMG Ponies May 08 '11 at 04:17
I kinda have to question the general approach, though. Could you instead have a cover function for this query and check the number of rows returned? On 0, you could output your default value constructed in the same way as your default record above. – MPelletier May 08 '11 at 18:20

score 0 · Answer 2 · answered May 08 '11 at 03:10

try working with LEFT JOIN statement. i'm probably not doing this 100% but a bit of trial and error on your part should make this work.

SELECT table1.field1, table1.field2, table2.field3, table2.field4
FROM my_table table1 
LEFT JOIN my_table table2 ON table1.field1=table2.field1 OR table1.field2=table2.field2

score 0 · Answer 3 · answered May 08 '11 at 12:05

a left join on a forced fixed value first table SHOULD work.

select 
      YourTable.color_id,
      ForcedSQL1Record.parent_id,
      ForcedSQL1Record.language_id,
      YourTable.name
   from 
      ( select 50 as Parent_ID,
                2 as Language_ID
            from YourTable
            limit 1 ) ForcedSQL1Record
      left join
         YourTable
            on ForcedSQL1Record.Parent_ID = YourTable.Parent_ID
            AND ForcedSQL1Record Language_ID = YourTable.Language_ID

SQL: How to return an non-existing row?

3 Answers3

Linked