[thelist] SQL - Complex Conditional Join? Help needed.

Joshua OIson joshua at alphashop.net
Sun Mar 18 14:04:07 CST 2001


I need some SQL help.  I've poured through my books but haven't had any
luck.  The basic situation is this, I have two tables, _type and _type_src.
Both tables have a column called "name".

Table _type has a FK called type_src_id to _type_src's id column.  Here are
the two table definitions:

_type
id IDENTITY PRIMARY KEY
type_src_id numeric(9) NULL
name charchar(100) NULL

_type_src
id IDENTITY PRIMARY KEY
name charchar(100) NULL

Here is some sample data:

_type
id, type_src_id, name
~~~~~~~~~~~~~~~
1, NULL, 'Email'
2, 1, 'Phone'

_type_src
~~~~~~~~~~~~~~
id, name
1, 'admin'

What I'm trying to do is to extract the name column from the _type table,
and the name column _type_src if the association exists.  In the data set
above the first record of _type doesn't have an association in the _type_src
table, but the second record does.  I want a query that returns

src, src_name
~~~~~~~~~~
'email', NULL
'Phone', 'admin'

I tried this:

SELECT
    _type.name,  _type_src.name AS src_name
  FROM _type, _type_src
  WHERE
       ((user_datum_type_src_id IS NULL)
         OR (user_datum_type_src_id = _user_datum_type_src.id
         AND _user_datum_type_src.app_id IS NULL))

But that doesn't work.  I get the same src_name for all records.  Any ideas?

-joshua






More information about the thelist mailing list