Convert SQLite join with multiple tables to PostgreSQL

Question

I have this dictionary thing and the table with translations. I can do a nice select using it with SQLite

SELECT e.slug,
       en.title,
       en.locale
FROM entities AS e
LEFT JOIN (
    locales AS en,
    entity_locales AS el 
) ON (
    el.entity_id = e.id
    AND el.locale_id = en.id
    AND en.locale == 'en'
)

That produces:

present,  translation, en
missing,  NULL,        NULL

But I can't convert it to Postgres because I don't understand what is going on when you specify more than one table in LEFT JOIN in SQLite:

SELECT e.slug,
       en.title,
       en.locale
FROM entities e
LEFT JOIN entity_locales el ON (el.entity_id = e.id)
JOIN locales en ON ( 
  el.locale_id = en.id
  AND en.locale = 'en'
)

Produces only

present,  translation, en

Is there a way to make it work?

Database structure in SQLite format:

CREATE TABLE IF NOT EXISTS "entities" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "slug" varchar
);

CREATE TABLE IF NOT EXISTS "entity_locales" (
  "entity_id" integer,
  "locale_id" integer
);

CREATE TABLE IF NOT EXISTS "locales" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "title" varchar,
  "locale" varchar
);

insert into entities(id, slug) values(1, 'present');
insert into entities(id, slug) values(2, 'missing');
insert into locales(id, title, locale) values(1, 'translation', 'en');
insert into entity_locales(entity_id, locale_id) values(1, 1);

Zohar Peled · Accepted Answer · 2017-08-17 09:44:50Z

3

You are using a left join only to the second table, and then you are using an inner join to the third. You need to use a left join to the product of the inner join between the second and third table.

Try this instead:

SELECT e.slug,
       en.title,
       en.locale
FROM entities e
LEFT JOIN 
(
    entity_locales el 
    JOIN locales en ON ( 
      el.locale_id = en.id
      AND en.locale = 'en'
)
) ON (el.entity_id = e.id)

btw, your initial script mixes implicit and explicit joins. I would advise against using implicit joins since explicit joins are a standard part of SQL for over 25 years now.

edited Aug 17, 2017 at 9:44

answered Aug 17, 2017 at 9:37

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

firedev Over a year ago

What about more than one locale at the same time, can I join entity_locales only once instead of repeating this as el1, el2 ?

Zohar Peled Over a year ago

Join once, change the condition from en.locale = 'en' to en.locale IN('en', 'fr', 'il')

firedev Over a year ago

Sorry for confusion I mean something like this gist.github.com/firedev/d4d0179a378416ab5725a8d6cdfcdf3f

Collectives™ on Stack Overflow

Convert SQLite join with multiple tables to PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related