Left join combined with full outer join

Question

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 1

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   │ ░░░░  │ ░░░░  ║
╚════╧═══════╧═════╧═════╧══════╧═══════╧═══════╝

Answer 2

You want left join with the condition on lang in the on clause:

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 and s.lang = l.lang;

This should be the Goldilock's solution, with just the right number of rows.

Or even:

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 and (s.lang = l.lang or s.lang is null);