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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuchs | 2002-06-10 19:10:02 | string functions |
Previous Message | Tom Lane | 2002-06-08 20:18:40 | Re: HELP! Cannot access table!!!! |