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','AAIN X','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.fiel d_name, dbtable.otherfi eld FROM value_list left
outer join dbtable on value_list.fiel d_name = dbtable.field_n ame

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]

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

KT,

If the value list is static, you could just do

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

I doubt that's the case though, so take a look at

http://www.aspfaq.com/show.asp?id=2248

-Andy

"KT" <kristin@master ypoint.com> wrote in message
news:f1bdb6c4.0 409241036.332ec 125@posting.goo gle.com...[color=blue]
> 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','AAIN X','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.fiel d_name, dbtable.otherfi eld FROM value_list left
> outer join dbtable on value_list.fiel d_name = dbtable.field_n ame
>
> 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[/color]

[K T]

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

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]

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

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]

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

> Given a arbitray one dimesional value list:[color=blue]
> ('AALGX','12345 ','XXXXX','AAIN X','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
>[/color]

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.colu mn_name


Christian.