I posted this message once through the Google Groups interface to pgsql.sql,
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=x.id RIGHT JOIN table2 AS y ON id2=y.id WHERE (date
IS NOT NULL AND (id1 = ? OR id2 = ?)) 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.
