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_at_oneneck.com> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]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