Selecting a non-unique column name in a WHERE clause

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

Selecting a non-unique column name in a WHERE clause

Balaji Ramanathan
Hi,

    Interesting problem I have run into.  I have a table called TRIP that
has various columns including origin and destination.  Both of these are
foreign key columns, referring to placeid's in the table PLACE that
contains things like placename, latitude, longitude, country, etc.

    So, the table structures are:

CREATE TABLE Trip (
TripID integer PRIMARY KEY ASC,
...
Origin integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
DELETE Cascade,
Destination integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
DELETE Cascade,
}

CREATE TABLE Place (
PlaceID integer PRIMARY KEY ASC,
PlaceName text NOT NULL,
Latitude float,
Longitude float,
Elevation float,
Country text,
)

    I have a view that pulls out all the columns of TRIP as well as details
of the origin and destination using two joints with PLACE.

CREATE VIEW TripDetailsRaw AS select *
from Trip
inner join Place P1 on Trip.Origin = P1.PlaceID
inner join Place P2 on Trip.Destination = P2.PlaceID;

    When I look at the output of this view (I type in "select * from
tripdetailsraw" at the sqlite command line, I see one set of columns from
P1 and another set of columns from P2 with ":1" appended to the repeated
column names ("PlaceName:1", "Country:1", etc.).  But I have no idea how to
add a WHERE clause to my select that references these columns.

    In the view itself, I can add a WHERE clause such as:

WHERE p1.country <> p2.country

    There is no problem there and the view produces the filtered rows
without any issues.

    But when I try to use the view in a query, those column names don't
work.  So, when I try to say "select * from TripDetailsRaw WHERE p1.country
<> p2.country", sqlite complains that those columns don't exist.
Similarly, I can't use "country:1", "country1", or "country_1", etc. in my
WHERE clause to refer to the country corresponding to P2 either.  In short,
I don't know how to refer to whatever column names sqlite is using
internally for these non-unique columns (even though, in the command line,
they are revealed to me as "country" and "country:1").

    Anyways, long description of a problem that has probably been
encountered and solved many times before.  If someone could give me the
answer or point me to the documentation that will allow me to figure the
answer out by myself, I would appreciate it.  Thank you.

Balaji Ramanathan
_______________________________________________
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: Selecting a non-unique column name in a WHERE clause

Igor Tandetnik-2
On 3/8/2018 10:41 PM, Balaji Ramanathan wrote:

> CREATE VIEW TripDetailsRaw AS select *
> from Trip
> inner join Place P1 on Trip.Origin = P1.PlaceID
> inner join Place P2 on Trip.Destination = P2.PlaceID;
>
>      When I look at the output of this view (I type in "select * from
> tripdetailsraw" at the sqlite command line, I see one set of columns from
> P1 and another set of columns from P2 with ":1" appended to the repeated
> column names ("PlaceName:1", "Country:1", etc.).  But I have no idea how to
> add a WHERE clause to my select that references these columns.

Don't use "select *" in the view. Explicitly select columns you need, assign aliases to them as needed. As in

select p1.country as OriginCountry, p2.country as DestinationCountry, ...

--
Igor Tandetnik


_______________________________________________
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: Selecting a non-unique column name in a WHERE clause

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
OK, sorry for spamming the list.  I figured it out a few minutes after I
sent that email.  The trick is to say:

where country <> "country:1"

It took me a bit of sleuthing, but using .mode insert helped because I
could see that the resulting insert query had these postfixed column names
enclosed in double-quotes.  I had tried single quotes (sqlite interpreted
those as strings to compare country against, so that did not work), but not
double-quotes in my various attempts.

But I have another question.  In the example I posted, the column was only
repeated twice and the repeated column had a ":1" at the end, but in my
actual query, I have some columns repeated multiple times, and I find that
some of the repeated columns have postfixes like ":52449101",
":3016716403", ":197485499", ":3551085770", etc.  Where do these postfixes
come from?  If I know a column is going to be repeated n times in my view,
can I predict what the column names in the resulting output will be?

Thank you.

Balaji Ramanathan

On Thu, Mar 8, 2018 at 9:41 PM, Balaji Ramanathan <
[hidden email]> wrote:

> Hi,
>
>     Interesting problem I have run into.  I have a table called TRIP that
> has various columns including origin and destination.  Both of these are
> foreign key columns, referring to placeid's in the table PLACE that
> contains things like placename, latitude, longitude, country, etc.
>
>     So, the table structures are:
>
> CREATE TABLE Trip (
> TripID integer PRIMARY KEY ASC,
> ...
> Origin integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
> DELETE Cascade,
> Destination integer NOT NULL references Place(PlaceID) ON UPDATE Cascade
> ON DELETE Cascade,
> }
>
> CREATE TABLE Place (
> PlaceID integer PRIMARY KEY ASC,
> PlaceName text NOT NULL,
> Latitude float,
> Longitude float,
> Elevation float,
> Country text,
> )
>
>     I have a view that pulls out all the columns of TRIP as well as
> details of the origin and destination using two joints with PLACE.
>
> CREATE VIEW TripDetailsRaw AS select *
> from Trip
> inner join Place P1 on Trip.Origin = P1.PlaceID
> inner join Place P2 on Trip.Destination = P2.PlaceID;
>
>     When I look at the output of this view (I type in "select * from
> tripdetailsraw" at the sqlite command line, I see one set of columns from
> P1 and another set of columns from P2 with ":1" appended to the repeated
> column names ("PlaceName:1", "Country:1", etc.).  But I have no idea how to
> add a WHERE clause to my select that references these columns.
>
>     In the view itself, I can add a WHERE clause such as:
>
> WHERE p1.country <> p2.country
>
>     There is no problem there and the view produces the filtered rows
> without any issues.
>
>     But when I try to use the view in a query, those column names don't
> work.  So, when I try to say "select * from TripDetailsRaw WHERE p1.country
> <> p2.country", sqlite complains that those columns don't exist.
> Similarly, I can't use "country:1", "country1", or "country_1", etc. in my
> WHERE clause to refer to the country corresponding to P2 either.  In short,
> I don't know how to refer to whatever column names sqlite is using
> internally for these non-unique columns (even though, in the command line,
> they are revealed to me as "country" and "country:1").
>
>     Anyways, long description of a problem that has probably been
> encountered and solved many times before.  If someone could give me the
> answer or point me to the documentation that will allow me to figure the
> answer out by myself, I would appreciate it.  Thank you.
>
> Balaji Ramanathan
>
_______________________________________________
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: Selecting a non-unique column name in a WHERE clause

Clemens Ladisch
Balaji Ramanathan wrote:
> I have some columns repeated multiple times, and I find that
> some of the repeated columns have postfixes like ":52449101",
> ":3016716403", ":197485499", ":3551085770", etc.  Where do these postfixes
> come from?

SQLite adds a number to make the name unique.  After three tries,
to speed up the search, it uses random numbers instead.

> can I predict what the column names in the resulting output will be?

No.  Igor already told you:

> Don't use "select *" in the view. Explicitly select columns you need,
> assign aliases to them as needed.


Regards,
Clemens
_______________________________________________
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: [EXTERNAL] Re: Selecting a non-unique column name in a WHERE clause

Hick Gunter
In reply to this post by Balaji Ramanathan
Using the shortcut '*' for a field list only work if you don't care about column names. SQLite will pick column names for you that may or may not be intuitive and that may change between releases of SQLite.

As soon as you find yourself trying to guess column names, you should throw away '*' and explicitly list which columns you need and give them names with the AS clause. Anything else will break sooner or later.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Balaji Ramanathan
Gesendet: Freitag, 09. März 2018 04:56
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] Selecting a non-unique column name in a WHERE clause

OK, sorry for spamming the list.  I figured it out a few minutes after I sent that email.  The trick is to say:

where country <> "country:1"

It took me a bit of sleuthing, but using .mode insert helped because I could see that the resulting insert query had these postfixed column names enclosed in double-quotes.  I had tried single quotes (sqlite interpreted those as strings to compare country against, so that did not work), but not double-quotes in my various attempts.

But I have another question.  In the example I posted, the column was only repeated twice and the repeated column had a ":1" at the end, but in my actual query, I have some columns repeated multiple times, and I find that some of the repeated columns have postfixes like ":52449101", ":3016716403", ":197485499", ":3551085770", etc.  Where do these postfixes come from?  If I know a column is going to be repeated n times in my view, can I predict what the column names in the resulting output will be?

Thank you.

Balaji Ramanathan

On Thu, Mar 8, 2018 at 9:41 PM, Balaji Ramanathan < [hidden email]> wrote:

> Hi,
>
>     Interesting problem I have run into.  I have a table called TRIP
> that has various columns including origin and destination.  Both of
> these are foreign key columns, referring to placeid's in the table
> PLACE that contains things like placename, latitude, longitude, country, etc.
>
>     So, the table structures are:
>
> CREATE TABLE Trip (
> TripID integer PRIMARY KEY ASC,
> ...
> Origin integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
> DELETE Cascade, Destination integer NOT NULL references Place(PlaceID)
> ON UPDATE Cascade ON DELETE Cascade, }
>
> CREATE TABLE Place (
> PlaceID integer PRIMARY KEY ASC,
> PlaceName text NOT NULL,
> Latitude float,
> Longitude float,
> Elevation float,
> Country text,
> )
>
>     I have a view that pulls out all the columns of TRIP as well as
> details of the origin and destination using two joints with PLACE.
>
> CREATE VIEW TripDetailsRaw AS select * from Trip inner join Place P1
> on Trip.Origin = P1.PlaceID inner join Place P2 on Trip.Destination =
> P2.PlaceID;
>
>     When I look at the output of this view (I type in "select * from
> tripdetailsraw" at the sqlite command line, I see one set of columns
> from
> P1 and another set of columns from P2 with ":1" appended to the
> repeated column names ("PlaceName:1", "Country:1", etc.).  But I have
> no idea how to add a WHERE clause to my select that references these columns.
>
>     In the view itself, I can add a WHERE clause such as:
>
> WHERE p1.country <> p2.country
>
>     There is no problem there and the view produces the filtered rows
> without any issues.
>
>     But when I try to use the view in a query, those column names
> don't work.  So, when I try to say "select * from TripDetailsRaw WHERE
> p1.country <> p2.country", sqlite complains that those columns don't exist.
> Similarly, I can't use "country:1", "country1", or "country_1", etc.
> in my WHERE clause to refer to the country corresponding to P2 either.
> In short, I don't know how to refer to whatever column names sqlite is
> using internally for these non-unique columns (even though, in the
> command line, they are revealed to me as "country" and "country:1").
>
>     Anyways, long description of a problem that has probably been
> encountered and solved many times before.  If someone could give me
> the answer or point me to the documentation that will allow me to
> figure the answer out by myself, I would appreciate it.  Thank you.
>
> Balaji Ramanathan
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users