Olaf Dietsche Olaf Dietsche - 3 months ago 20
SQL Question

Left join combined with full outer join

There is a main table, which has references to two other tables. These two tables might have multiple entries per reference.

Main:

| id | mname | sid | lid |
|----|-------|-----|-----|
| 1 | a1 | 1 | 2 |
| 2 | a2 | 2 | 3 |
| 3 | a3 | 1 | 1 |


Short:

| id | lang | sdesc |
|----|------|-------|
| 1 | de | S1 |
| 1 | en | S2 |
| 2 | de | S3 |
| 3 | en | S4 |


(id, lang)
is unique.

Long:

| id | lang | ldesc |
|----|------|-------|
| 1 | de | L1 |
| 1 | en | L2 |
| 2 | de | L3 |
| 3 | en | L4 |


(id, lang)
is unique.

I want to join these three tables to have the following result:

| mname | lang | sdesc | ldesc |
|-------|------|--------|--------|
| a1 | de | S1 | L3 |
| a1 | en | S2 | (null) |
| a2 | de | S3 | (null) |
| a2 | en | (null) | L4 |
| a3 | de | S1 | L1 |
| a3 | en | S2 | L2 |





My first try was (sqlfiddle)

select m.mname, s.lang, s.sdesc, l.lang, l.ldesc
from main m
left join short_desc s on s.id = m.sid
left join long_desc l on l.id = m.lid


which gives way too many entries.

Next one was (sqlfiddle)

select m.mname, s.lang, s.sdesc, l.lang, l.ldesc
from main m
left join short_desc s on s.id = m.sid
left join long_desc l on l.id = m.lid
where s.lang = l.lang


which leaves out valid entries.

After many variations of the above and a lot of playing around, I came up with (sqlfiddle)

with x1 as (select m.id, m.mname, s.lang, s.sdesc
from main m
join short_desc s on s.id = m.sid),
x2 as (select m.id, m.mname, l.lang, l.ldesc
from main m
join long_desc l on l.id = m.lid)
select coalesce(x1.mname, x2.mname) mname,
coalesce(x1.lang, x2.lang) lang,
x1.sdesc,
x2.ldesc
from x1
full outer join x2 on x2.id = x1.id and x2.lang = x1.lang


which gives the desired result, but to me this seems excessive for such a simple (?) requirement.

So here's my question, is there a simpler approach?

Answer

IMO what you are missed in the your data schema is the dictionary of the available languages. If introduce it, all other becomes simpler.

The desired dictionary cold be:

  • the simple constant like array['en','de','fr']
  • the true table
  • the view collecting language codes from tables like short_desc, long_desc etc

It is up to you which case is more appropriate for your purposes.

In the example below we consider the languages dictionary as CTE:

with lang(code) as (values('en'),('de'),('fr'))
select m.*, lang.code
from main m cross join lang;

╔════╤═══════╤═════╤═════╤══════╗
║ id │ mname │ sid │ lid │ code ║
╠════╪═══════╪═════╪═════╪══════╣
║  1 │ a1    │   1 │   2 │ en   ║
║  2 │ a2    │   2 │   3 │ en   ║
║  3 │ a3    │   1 │   1 │ en   ║
║  1 │ a1    │   1 │   2 │ de   ║
║  2 │ a2    │   2 │   3 │ de   ║
║  3 │ a3    │   1 │   1 │ de   ║
║  1 │ a1    │   1 │   2 │ fr   ║
║  2 │ a2    │   2 │   3 │ fr   ║
║  3 │ a3    │   1 │   1 │ fr   ║
╚════╧═══════╧═════╧═════╧══════╝
(9 rows)

As you can see, now we have the individual row for each language and the next and the last step is join other two tables to the query above using id and lang fields:

with lang(code) as (values('en'),('de'),('fr'))
select m.*, lang.code, s.sdesc, l.ldesc
from main m cross join lang
  left join short_desc s on s.id = m.sid and s.lang = lang.code
  left join long_desc l on l.id = m.lid and l.lang = lang.code;

╔════╤═══════╤═════╤═════╤══════╤═══════╤═══════╗
║ id │ mname │ sid │ lid │ code │ sdesc │ ldesc ║
╠════╪═══════╪═════╪═════╪══════╪═══════╪═══════╣
║  3 │ a3    │   1 │   1 │ de   │ S1    │ L1    ║
║  3 │ a3    │   1 │   1 │ en   │ S2    │ L2    ║
║  3 │ a3    │   1 │   1 │ fr   │ ░░░░  │ ░░░░  ║
║  1 │ a1    │   1 │   2 │ de   │ S1    │ L3    ║
║  1 │ a1    │   1 │   2 │ en   │ S2    │ ░░░░  ║
║  1 │ a1    │   1 │   2 │ fr   │ ░░░░  │ ░░░░  ║
║  2 │ a2    │   2 │   3 │ de   │ S3    │ ░░░░  ║
║  2 │ a2    │   2 │   3 │ en   │ ░░░░  │ L4    ║
║  2 │ a2    │   2 │   3 │ fr   │ ░░░░  │ ░░░░  ║
╚════╧═══════╧═════╧═════╧══════╧═══════╧═══════╝