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 <
balaji.ramanat...@gmail.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
