On 2017/08/17 8:54 PM, Lars Frederiksen wrote:
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
It means the Field SENTENCE_EN appears in more than one table and you
did not qualify it, os the Query planner doesn't know which one you mean.
Try:
SELECT
Saetning.SENTENCE_EN...
- reason of this error?
- is inner joins the correct way to "connect" two tebles via a "link-table"?
Seems weird, it is better to put the filter references to a joined table
AFTER the table is actually joined, but more importantly, where is table
"Verbum" table being joined? You refer to it, but it's not in the join list.
Did you perhaps mean to do this?:
SELECT
Saetning.SENTENCE_EN,
Saetning.SENTENCE_EL
FROM Saetning
INNER JOIN Verb_Saetn ON Verb_Saetn.Saetning_ID = Saetning.Saetning_ID
INNER JOIN Verbum ON Verbum.Verbum_ID = Verb_Saetn.Verbum_ID;
Also - SQLite is very forgiving in terms of Case-sensitivity for Field, table
and general object identifiers, but other engines will not know that Verbum_ID
and VERBUM_ID is the same thing, and error out. Check your consistency.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
