firedev firedev - 1 year ago 43
SQL Question

Convert SQLite join with multiple tables to PostgreSQL

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);

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download