Re: SQL Server 2000 and MS Access 2002 UPDATE anomalies

Not fully cartesian, as that would return 55 rows.

The value used to do the lookup does not fully qualify
the key in the NAME_LOOKUP table, causing several
rows to be returned in many cases,  so maybe cartesian
is the wrong word to use.

The tables are somewhat contrived to make a simple example,
but I took this from a real life scenario.

I was not terribly surprised that Access allowed this, but
was surprised that SQL Server would.



Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


On 9/29/05, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
> I don't believe that is a cartesian product - since you have the join 
> condition "where n.id = nl.id".  If you removed that, then you would have a 
> cartesian, which would result in 5*11=55 rows returned.  Right, or am I 
> missing something?
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jared Still
>
>
>
> The following SQL returns a cartesian product as you would expect:
>
> 16:26:41 SQL>l
>   1  select n.id, nl.name
>   2  from names n, name_lookup nl
>   3* where n.id = nl.id
> 16:26:41 SQL>/
--
http://www.freelists.org/webpage/oracle-l

Other related posts: