Message-ID: <43C7BA12.90103@rosi-kessel.org>
Date: Fri, 13 Jan 2006 09:32:50 -0500
From: Adam Rosi-Kessel <adam@rosi-kessel.org>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: ORDER BY does not work as expected with multiple joins
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigA2B837EBEA880FC95F43D3DB"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigA2B837EBEA880FC95F43D3DB
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I posted this message once through the Google Groups interface to pgsql.s=
ql,
but I've received no replies and the message doesn't appear at all in the=

MHonArc archives at postgresql.org, so I'm not sure it actually was
distributed to this list. Apologies if this is a duplicate:

I have a multijoin SELECT statement where the ORDER BY clause doesn't
seem to be working. I can't figure out how pgsql is sorting the
results, but it is definitely different from the same list without the
multiple joins.

To simplify: I have two tables. The first table ("table1") has three
fields: date, id1, and id2. The data look like this:

Date     ID1   ID2
1/5/06   2     1
1/1/06   1     2
1/3/06   1     3

The second table ("table2") has two fields: id and name. The data look
like this:

ID   NAME
1    John
2    Jane
3    Sam

What I am trying to do is join the two tables and sort by the date.
ID1 and ID2 in table1 *both* reference ID in table2.  The desired
result of the SELECT statement would look like this:

Date     name1  name2
1/1/06   John     Jane
1/3/06   John     Sam
1/5/06   Jane     John

For some reason, the result is not properly sorting by date--the data
are being reordered, but not in a way I can understand.

Here is the SELECT statement:

SELECT date,x.name as name1,y.name AS name2 FROM table1 LEFT JOIN
table2 AS x ON id1=3Dx.id RIGHT JOIN table2 AS y ON id2=3Dy.id WHERE (dat=
e
IS NOT NULL AND (id1 =3D ? OR id2 =3D ?)) ORDER BY date;

(where ? is replaced by the desired ID -- I want the results to list
all instances in table1 where id1 or id2 is a given ID number).

Did I mess up the multiple JOINs?  It seems like the SELECT is giving
the correct results other than not ordering by DATE.


--------------enigA2B837EBEA880FC95F43D3DB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFDx7oVdTf3ZklQ6qYRAo96AJ4rEzjVim4Fsrt0gxeRBmVxyN/ZsgCeJLBa
aBecnzF/xf4TU/oyXTltx3w=
=HF+P
-----END PGP SIGNATURE-----

--------------enigA2B837EBEA880FC95F43D3DB--