By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,914 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,914 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Jul 20 '05 #2

P: n/a
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!
Jul 20 '05 #3

P: n/a
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!
Jul 20 '05 #4

P: n/a
> 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.
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.