I'm attempting to write a query against a table (Table_A) which has three columns. Column_1 lists the names of...
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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.