[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