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
