Skip site navigation (1) Skip section navigation (2)

ORDER BY does not work as expected with multiple joins

From: Adam Rosi-Kessel <adam(at)rosi-kessel(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: ORDER BY does not work as expected with multiple joins
Date: 2006-01-13 14:32:50
Message-ID: 43C7BA12.90103@rosi-kessel.org (view raw or flat)
Thread:
Lists: pgsql-sql
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.

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2006-01-13 14:56:08
Subject: Re: ORDER BY does not work as expected with multiple joins
Previous:From: Tom LaneDate: 2006-01-13 05:10:29
Subject: Re: [SQL] info is a reserved word?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group