Yes. Or you could toss the new fangled JOIN syntax (which does nothing if the
join is not an outer join) which only adds silly complication and do something
like:
SELECT sentece_en,
sentence_el
FROM Saetning, Verb_Saetn, Verbum
WHERE Saetning.Saeting_ID == Vert_Saetn.Saetning_ID
AND Verb_Saetn.VERBUM_ID == Verbum.VERBUM_ID;
Much more obvious what you are doing. Symantically it is exactly the same as
David's query in the new fangled syntax since that query is merely written into
the form above (the keyword JOIN or INNER JOIN is merely replaced by a ",", and
the conditions in ON <condition> are merely pushed into the where clause and
joined by AND).
Since the contents of the ON clause are pushed down as where conditions, there
is absolutely no requirement for the tables used in the ON clauses to have been
seen prior to there use. They only need to exist when the optimizer attempts
to generate a query plan (at least this is the case for all sane SQL
processors, of which SQLite is one).
You need to have indexes on your "foreign" key fields, and you probably want an
index on Vert_Saetn containing the link keys Saetning_ID and VERBUM_ID,
probably two indexes with the fields in both orders. Of course, that depends
if you database will ever hold more than 10 records per table (if it will, then
you will need all the indexes).
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[email protected]] On Behalf Of Lars Frederiksen
>Sent: Thursday, 17 August, 2017 13:53
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] Error trying to do inner joins
>
>Thank you,
>
>This SQL works fine.
>Is the inner join normally used for connecting 3 tables where the
>"middle one" is a link table?
>
>/Lars
>
>
>
>-----Oprindelig meddelelse-----
>Fra: sqlite-users [mailto:sqlite-users-
>[email protected]] På vegne af David Raymond
>Sendt: 17. august 2017 21:16
>Til: SQLite mailing list
>Emne: Re: [sqlite] Error trying to do inner joins
>
>There's an issue with the join statement in the first join you're
>joining Saetning and Verb_Saetn, but the ON clause uses fields in
>Verb_Saetn and Verbum, not Saetning. And then you join that back to
>Saetning, so you've joined Saetning twice and Verbum 0 times. Since
>the Saetning table shows up twice in your join that's why the fields
>are ambiguous.
>
>Try...
>
>...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID =
>Verb_Saetn.SAETNING_ID INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID =
>Verbum.VERBUM_ID
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[email protected]] On Behalf Of Lars Frederiksen
>Sent: Thursday, August 17, 2017 2:54 PM
>To: [email protected]
>Subject: [sqlite] Error trying to do inner joins
>
>Hello,
>
>
>
>I have a small db with 3 tables:
>
>
>
>
>
>CREATE TABLE IF NOT EXISTS "Verb_Saetn"(
>
>VS_ID INT PRIMARY KEY NOT NULL,
>
>VERBUM_ID INT NOT NULL,
>
>SAETNING_ID INT NOT NULL,
>
>FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID),
>
>FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID)
>
>);
>
>
>
>CREATE TABLE IF NOT EXISTS "Verbum"(
>
> VERBUM_ID INT PRIMARY KEY NOT NULL,
>
> NUTID VARCHAR(40) NULL,
>
> DATID_IMPF VARCHAR(40) NULL,
>
> DATID_PERF VARCHAR(40) NULL,
>
> FREMTID_IMPF VARCHAR(40) NULL,
>
> FREMTID_PERF VARCHAR(40) NULL,
>
> GUL INTNOT NULL,
>
> TYPE VARCHAR(20) NOT NULL,
>
> NOTE TEXT NULL
>
>);
>
>
>
>CREATE TABLE IF NOT EXISTS "Saetning"(
>
>SAETNING_ID INT PRIMARY KEY NOT NULL,
>
>SENTENCE_EN VARCHAR(200) NOT NULL,
>
>SENTENCE_EL VARCHAR(200) NOT NULL
>
>);
>
>
>
>
>
>The Verb_Saetn table is a link table between Verbum and Saetning
>(sentence in danish).
>
>I try this SQL text:
>
>
>
>
>
>SELECT
>
>SENTENCE_EN,
>
>SENTENCE_EL
>
>FROM Saetning
>
>INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID
>
>INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID;
>
>
>
>But get this error: ambiguous column name: SENTENCE_EN
>
>
>
>- reason of this error?
>
>- is inner joins the correct way to "connect" two tebles via a "link-
>table"?
>
>
>
>Best Regards
>
>Lars
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
