Re: Weird behaviour on a join with multiple keys

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/08/07 16:09, Charlie Clark wrote:
> Hi,
>
> I'm getting unexpected results on a query which involves joining two
> tables on two common variables (firstname and lastname).
>
> This is the basic query:
>
> SELECT table1.lastname, table1.firstname
> FROM table1
> INNER JOIN table2 ON
> (table2.name = table1.name
> AND
> table2.vorname = table1.vorname)
>
> This is returning many rows fewer than I expect and is ignoring a lot
> where table1.firstname = table2.firstname AND table1.lastname =
> table2.lastname.

Huh? Why should you? You're not joining on firstname and lastname.

What happens if you do it like this:
SELECT T1.LASTNAME, T2.FIRSTNAME
FROM TABLE1 T1,
TABLE2 T2
WHERE T1.NAME = T2.NAME
AND T1.VORNAME = T2.VORNAME;

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8N1zS9HxQb37XmcRAp3wAKCRJ1kuoqbc8YPOZwx+53+JRqvD/ACfVvFy
zK8u0+RYuMiBxEnURVc74Jc=
=9oxj
-----END PGP SIGNATURE-----