Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using a pre-existing list

I'm attempting to write a query against a table (Table_A) which has three columns. I have a pre-existing list that I'm attempting to bounce off my table. I want, however, all of the names in my pre-existing list to appear in my results. If the criterion doesn't exist, it should say "NULL."

I'm attempting to write a query against a table (Table_A) which has three columns. Column_1 lists the names of...

vehicle manufacturers.

Column_1     Column_2     Column_3
Ford         Blue         4door
Chevy        Blk          2door
Honda        Blue         4door
Nissan       Red          2door

I have a pre-existing list that I'm attempting to bounce off my table. I want, however, all of the names in my pre-existing list to appear in my results. If the criterion doesn't exist, it should say "NULL." My pre-existing list looks like this:

Ford
Chevy
Honda
Nissan
Toyota
Jeep

I want to select every record where Column_2 is Blue and Column_3 is 4door. I want my results to look like this:

Vehicle_Make    Column_2        Column_3
Ford            Blue            4door
Chevy           null            null
Honda           Blue            4door  
Nissan          null            null
Toyota          null            null
Jeep            null            null

What would the SQL look like?

Unfortunately, the exact SQL depends on which database system you're using. Here's the basic strategy:

select X.Vehicle_Make
     , Table_A.Column_2
     , Table_A.Column_3
  from ( select 'Ford' as Vehicle_Make
         union all
         select 'Chevy'
         union all
         select 'Honda'
         union all
         select 'Nissan'
         union all
         select 'Toyota'
         union all
         select 'Jeep'
       ) as X
left outer
  join Table_A
    on Table_A.Column_1 = X.Vehicle_Make
   and Table_A.Column_2 = 'Blue'
   and Table_A.Column_3 = '4door'

Some database systems don't allow you to select columns out of thin air, as it were. For example, in Oracle you need to select the X columns from a table called DUAL.

This was last published in April 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close