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

Re: Conditional Join (or something along those lines)

From: "Joel Burton" <joel(at)joelburton(dot)com>
To: "von Schwerdtner, Thomas (GXS, FPI)" <Thomas(dot)vonSchwerdtner(at)gxs(dot)ge(dot)com>,"PostgreSQL (E-mail)" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Conditional Join (or something along those lines)
Date: 2002-06-10 15:14:19
Message-ID: 004c01c21091$7e769c40$63eebed1@jburtonsat3005 (view raw or flat)
Thread:
Lists: pgsql-novice
Your SQL:

SELECT
    r.region_name, r.region_no,
    cj.relationship,
    c.name_last,
    c.name_first,
    c.email
FROM
    ((region r
LEFT JOIN
    contact_join cj
ON
    ((cj.parent_id = r.region_id)))
LEFT JOIN
    contact c
ON
    ((c.id = cj.contact_id)))
WHERE
    (cj.parent_table = 'region');



The WHERE clause will get rid of all the work of your LEFT JOINS, since
parent_table won't = 'region' for the non-present halves, it will be null. A
kludgy solution would be to change the "WHERE parent_table = 'region'" to
"WHERE parent_table = 'region' OR parent_table IS NULL"; a much better
solution would be to change the ON clause of the first LEFT JOIN to
"parent_id = region_id AND parent_table = 'region'"

I haven't tested this -- in the future, if possible, it's nice to post a
small dump of your problem set (in addition to your clear explanation),
allowing list members to recreate your tables by just cutting & pasting the
DDL statements to test our advice.

- J.


In response to

pgsql-novice by date

Next:From: Michael FuchsDate: 2002-06-10 19:10:02
Subject: string functions
Previous:From: Tom LaneDate: 2002-06-08 20:18:40
Subject: Re: HELP! Cannot access table!!!!

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