Represent a value list as a table for Outer Join????

[KT]

This might not be possible, but on the chance that it can - is there a
way to do the following:

Given a arbitray one dimesional value list:
('AALGX','12345','XXXXX','AAINX','AMMXX')

Is there a way that I could do a select statement, or similiar, in the
value list, to get the following result

field_name
-----------
AALGX
12345
XXXXX
AAINX
AMMXX

Because, what I want to be able to do in the long run is essentially
perform an outer join on the value list.

Something along the lines of

select value_list.field_name, dbtable.otherfield FROM value_list left
outer join dbtable on value_list.field_name = dbtable.field_name

So I want all the values in the field list to show up, and any
matching data in the database table that exists, otherwise null.

Maybe there is another approach to this???

Thanks!
KT

[Andy Williams]

KT,

If the value list is static, you could just do

SELECT field_name, otherfield
FROM dbtable
WHERE field_name IN ('AALGX','12345','XXXXX','AAINX','AMMXX')

I doubt that's the case though, so take a look at
http://www.aspfaq.com/show.asp?id=2248

-Andy

"KT" <kr*****@masterypoint.com> wrote in message
news:f1**************************@posting.google.c om...

This might not be possible, but on the chance that it can - is there a
way to do the following:

Given a arbitray one dimesional value list:
('AALGX','12345','XXXXX','AAINX','AMMXX')

Is there a way that I could do a select statement, or similiar, in the
value list, to get the following result

field_name
-----------
AALGX
12345
XXXXX
AAINX
AMMXX

Because, what I want to be able to do in the long run is essentially
perform an outer join on the value list.

Something along the lines of

select value_list.field_name, dbtable.otherfield FROM value_list left
outer join dbtable on value_list.field_name = dbtable.field_name

So I want all the values in the field list to show up, and any
matching data in the database table that exists, otherwise null.

Maybe there is another approach to this???

Thanks!
KT

[K T]

Right - that is the way to do it as an inner join, getting only the
records that match, but I want to use a value list in an outer join - is
that possible??

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[K T]

Andy -

Actually, I was able to use that link you sent along to accomplish the
outer join part of the goal! Thanks - that was really helpful!!!
KT

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Christian Maslen]

> Given a arbitray one dimesional value list:

('AALGX','12345','XXXXX','AAINX','AMMXX')

Is there a way that I could do a select statement, or similiar, in the
value list, to get the following result

field_name
-----------
AALGX
12345
XXXXX
AAINX
AMMXX

Hi KT,

Try the following:

SELECT *
FROM (
SELECT 'AALGX'
UNION ALL
SELECT '12345'
UNION ALL
SELECT 'XXXXX'
UNION ALL
SELECT 'AAINX'
UNION ALL
SELECT 'AMMXX'
) AS value_list (column_name)

So your outer join would look something like:

SELECT *
FROM other_table o
LEFT OUTER JOIN
(
SELECT 'AALGX'
UNION ALL
SELECT '12345'
UNION ALL
SELECT 'XXXXX'
UNION ALL
SELECT 'AAINX'
UNION ALL
SELECT 'AMMXX'
) AS value_list (column_name)
ON o.column_name = value_list.column_name
Christian.