Re: [sqlite] How can I specify that a column is equal to another?

2009-07-01 Thread Dennis Cote 
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?

2009-07-01 Thread Pavel Ivanov 
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?

2009-07-01 Thread Simon Slavin 

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?

2009-07-01 Thread Yuzem 

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