Re: [sqlite] How can I specify that a column is equal to another?
Yuzem wrote: > Is there any way to specify that movies.id is equal to user.id so I can use > just id in my query? > Thanks in advance! > Not with a left join, but with an inner join you can use the USING clause or a NATURAL join. See http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join for more details. Note that SQLite does not report an error when you use a table name qualifier for a column named in a using clause or paired off in a natural join as it should. This may cause compatibility issues with other database programs. Using your example, you could use either of the following queries. select title,my_rating from movies join user using(id) where id = 'tt0426459'; select title,my_rating from movies natural join user where id = 'tt0426459'; According to the SQL standard, these should all produce an error since the qualified column doesn't exist in the join's result table. select title,my_rating from movies join user using(id) where movies.id = 'tt0426459'; select title,my_rating from movies join user using(id) where user.id = 'tt0426459'; select title,my_rating from movies natural join user where movies.id = 'tt0426459'; select title,my_rating from movies natural join user where user.id = 'tt0426459'; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I specify that a column is equal to another?
You can do only where movies.id = 'tt0426459' or where user.id = 'tt0426459' What to choose depends on your needs. And you're wrong that these variants are identical and movies.id is always equal to user.id because you're making left join. They will be identical if you will make inner join. But even in this case I don't understand why you consider bad or inconvenient explicitly mentioning table name. Pavel On Wed, Jul 1, 2009 at 12:24 PM, Yuzem wrote: > > I have this: > select title,my_rating > from movies left join user on movies.id = user.id > where id = 'tt0426459' > The result: > ambiguous column name: id > > I could use: > select movies.id ids,title,my_rating > from movies left join user on movies.id = user.id > where ids = 'tt0426459' > > but I don't want to select the id > > Another solution: > where movies.id = 'tt0426459' > > Is there any way to specify that movies.id is equal to user.id so I can use > just id in my query? > Thanks in advance! > -- > View this message in context: > http://www.nabble.com/How-can-I-specify-that-a-column-is-equal-to-another--tp24292794p24292794.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I specify that a column is equal to another?
On 1 Jul 2009, at 5:24pm, Yuzem wrote: > Is there any way to specify that movies.id is equal to user.id so I > can use > just id in my query? I think you have already come up with the two best solutions. You could also create a VIEW http://www.sqlite.org/lang_createview.html and specify which 'id' you wanted when defining the view. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How can I specify that a column is equal to another?
I have this: select title,my_rating from movies left join user on movies.id = user.id where id = 'tt0426459' The result: ambiguous column name: id I could use: select movies.id ids,title,my_rating from movies left join user on movies.id = user.id where ids = 'tt0426459' but I don't want to select the id Another solution: where movies.id = 'tt0426459' Is there any way to specify that movies.id is equal to user.id so I can use just id in my query? Thanks in advance! -- View this message in context: http://www.nabble.com/How-can-I-specify-that-a-column-is-equal-to-another--tp24292794p24292794.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users