Error trying to do inner joins

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Error trying to do inner joins

larshgf
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Error trying to do inner joins

R Smith


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Error trying to do inner joins

David Raymond
In reply to this post by larshgf
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:[hidden email]] On Behalf Of Lars Frederiksen
Sent: Thursday, August 17, 2017 2:54 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Error trying to do inner joins

larshgf
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:[hidden email]] 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:[hidden email]] On Behalf Of Lars Frederiksen
Sent: Thursday, August 17, 2017 2:54 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Error trying to do inner joins

Keith Medcalf

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-
>[hidden email]] 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-
>[hidden email]] 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-
>[hidden email]] On Behalf Of Lars Frederiksen
>Sent: Thursday, August 17, 2017 2:54 PM
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Error trying to do inner joins

David Raymond
In reply to this post by larshgf
Correct. For normal* cases inner joins are where you want only things that match in table A and table B, then you expand that out with multiple inner joins to get to 3 or more tables. Order doesn't matter in an inner join, so A INNER JOIN B will be equivalent to B INNER JOIN A.


Outer joins are where you want everything that matches in A and B, but also want everything from table A that didn't match to B. Outer joins are order sensitive, so A LEFT OUTER JOIN B is different than B LEFT OUTER JOIN A


* "Normal" being equi-joins of the type ...ON A.x = B.x (and A.y = B.y...)
You can throw inequalities into the ON clause there, or just about any statement that can be cast to a boolean in the end, but 99% of the time you're gonna see just the = operator.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Lars Frederiksen
Sent: Thursday, August 17, 2017 3:53 PM
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:[hidden email]] 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:[hidden email]] On Behalf Of Lars Frederiksen
Sent: Thursday, August 17, 2017 2:54 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users