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

Conditional Join (or something along those lines)

From: "von Schwerdtner, Thomas (GXS, FPI)" <Thomas(dot)vonSchwerdtner(at)gxs(dot)ge(dot)com>
To: "PostgreSQL (E-mail)" <pgsql-novice(at)postgresql(dot)org>
Subject: Conditional Join (or something along those lines)
Date: 2002-06-07 16:21:47
Message-ID: 3D808EC801AED111B0100008C75D5DDC125DE62B@roc05bxgeisge.is.ge.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hey folks,

  I'm what I might call a "novice hack" at SQL in general, so forgive any
blatant oversighs on my part.

  My problem is this, I have three tables as such:

region
===========
region_id
region_name
region_no

contact
===========
contact_id
name_first
name_last
email

contact_join
============
contact_id
parent_id
parent_table
relationship

The contact_join table works by holding the name of the table the contact is
for in the parent_table field, and the id of the record in that table.  

A simple example of the three tables might be:

region:
23, 'South / Florida', '234'

contact:
11, 'John', 'Doe', 'generic(at)aol(dot)com'

contact_join:
11, 23, 'region', 'Manager'


There are numerous tables that a record in the contact table might be
connected to which is why I have the parent_table field.  If there is a
better way to do this, I'm all ears, but I'd rather my question were answerd
since I dont want to rewrite the code I have already....which leads me to
the question:

I want a view that will show all the relevant information about a region,
namely the regino_name, region_no, c.name_first, c.name_last, c.email and
cj.relationship (contact c, contact_join cj). I have come up with this query
(I'm new at joins if you couldnt guess):

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 problem is, sometimes no contact information has been entered yet, and I
need to be able to see regions even if they have no contact information.
The above query will only show items that have a contact_join entry with the
parent_table as 'region'. 

In pseudocode, I want this:

SELECT /* region data from region table, and if contact information exists
for a region, select the contact information as well, otherwise leave the
contact fields blank */

Follow?  Let me know if this dosent make any sense.

Any help would be appriciated.

Regards
-Tom

---------------------------------
 Tom von Schwerdtner
 Webmaster, Facilities Plus Inc.
 301-340-5101
---------------------------------

Responses

pgsql-novice by date

Next:From: Mark ThomasDate: 2002-06-07 18:27:44
Subject: PQescapeString
Previous:From: WimDate: 2002-06-07 14:20:18
Subject: HELP! Cannot access table!!!!

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