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
