CODEWITHSUNDEEP
sqloracle-database
Harry
Harry

Reputation: 782

SQL Left Join - Join on one condition, default to another condition

I'm doing a join between two tables:

SELECT * FROM Thing thing
    LEFT OUTER JOIN ThingDetails details 
        ON (a.id = details.thing_id AND details.thing_language = 'Spanish')

This will basically give me all entries in table 'Thing' with details in Spanish - if there is no details in Spanish, it will give me null values.

What I want to do is "fallback" to English for a Thing if there is no entry in ThingDetails for Spanish i.e. if ON (a.id = details.thing_id AND details.thing_language = 'Spanish') fails.

Is there a way I can easily accomplish this? Essentially knowing if the right side of the left join is null?

Upvotes: 1

Views: 554

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use two left joins and `coalesce()

SELECT t.*,
       COALESCE(tds.col, tde.col) as as col
FROM Thing t LEFT JOIN
     ThingDetails tds 
     ON t.id = tds.thing_id AND tds.thing_language = 'Spanish' LEFT JOIN
     ThingDetails tde
     ON t.id = tde.thing_id AND tde.thing_language = 'English' AND tds.thing_id IS NULL;

The LEFT JOINs bring the data together. The COALESCE() chooses the appropriate value of the column you care about. Add more COALESCE() rows if you have more columns that you care about.

EDIT:

If you don't want to list the columns, you can do:

select t.*, td.*
from Thing t left joi
     (select td.*,
             row_number() over (partition by thing_id
                                order by case when td.thing_language = 'Spanish' then 1 else 2 end
                               ) as seqnum
      from ThingDetails td
      where thing_language in ('Spanish', 'English')
     ) td
where seqnum = 1;

Upvotes: 1

JNevill
JNevill

Reputation: 50064

You can join to the table twice. Once for your desired language and once for your default language. Then use COALESCE() in your select to do the defaulting:

SELECT thing.*, COALESCE(details_spanish.some_field, details_default.some_field) as some_field 
FROM Thing thing
    LEFT OUTER JOIN ThingDetails details_spanish
        ON (a.id = details.thing_id AND details.thing_language = 'Spanish')
    LEFT OUTER JOIN ThingDetails details_default 
        ON (a.id = details.thing_id AND details.thing_language = 'English')

Upvotes: 2

Related Questions