Thread: Conditional Join (or something along those lines)

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@aol.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
---------------------------------

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.