CODEWITHSUNDEEP
sqloracle-databasejoin
user3260813
user3260813

Reputation: 59

Get default value or value from table in SQL

I have a 1:n relation in two tables. And some default value for Lang Id.

+========================+
|        Table A         |
+==========+======+======+
| Lang ID. | FKB. | Text |
+----------+------+------+
| 1        | 2.   | Q    |
| 1        | 3.   | W    |
| 2        | 3.   | E    |
| 2        | 4.   | R    |
+==========+======+======+

+=========+
| Table B |
+=========+
| IDB.    |
+---------+
| 2       |
| 3       |
| 4       |
+=========+

I want to write SQL to get Text, for all elements in B for language with id 1. If there is no language 1 for some element, then another language should be taken.

The following select is not correct

 Select * from B join A on IDB = FKB and langid = 1

Expected results:

Q
W
R

Upvotes: 0

Views: 80

Answers (2)

El_L
El_L

Reputation: 355

Select * from B join (

(SELECT A.[fkb],A.[text]
FROM A
INNER JOIN
    (SELECT  min([langid])  as lang,fkb
    FROM A
    GROUP BY [fkb]) help 
ON A.[fkb] = help.[fkb]
AND A.[langid]=help.lang)) helpA
on b.IDB = helpA.FKB

Upvotes: 0

Codo
Codo

Reputation: 78855

Try it with the following query:

SELECT b.idb, COALESCE(a.text, a2.text) text
FROM b
LEFT JOIN a ON b.idb = a.fkb AND a.langid = 1
LEFT JOIN (
  SELECT fkb, text
  FROM (
    SELECT fkb, text,
        ROW_NUMBER() OVER (PARTITION BY kfb ORDER BY langid) seq_no
    FROM a
  ) t
  WHERE seq_no = 1
) a2 ON b.idb = a2.fkb

The query joins both to the table A (restricted to langid = 1) and to an inner view, which basically filters the first text for each FKB in table A.

COALESCE then combines the text from A and the inner view, preferring the result from A over the one from the inner view, which is the fallback.

Upvotes: 1

Related Questions