Posted by David Portas on 03/17/06 22:00

alex wrote:
> Thanks for the response. Let me pose the example this way:
>
> I'm attempting to write a query against a table (Table_A). Let's
> say the table has three columns with various records. Column_1 lists
> the names of vehicle manufacturers. I have a preexisting list that
> I'm attempting to bounce of my table. I want, however, all of the
> names in my preexisting list to appear in my results. If the criterion
> doesn't exit, it should say 'NULL'. Thanks for any help.
>
> For example:
>
> Table_A
>
> COLUMN_1 COLUMN_2 COLUMN_3
> Ford Blue 4door
> Chevy Blk 2door
> Honda Blue 4door
> Nissan Red 2door
>
> My preexisting list looks like this:
>
> Ford
> Chevy
> Honda
> Nissan
> Toyota
> Jeep
>
> I want to select every record from COLUMN_2 and _3 that = Blue and
> 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

Please post DDL in future otherwise you are forcing us to guess what
your datatypes, keys, constraints and RI look like. The best way to
post sample data is as INSERT statements.

What is the "preexisting list" in your example? There is no list or
array data structure in SQL. The most sensible way to supply that set
of values is in a table. Use a table variable if you don't have a
permanent table. Example:

CREATE TABLE table_a (vehicle_make VARCHAR(10) NOT NULL PRIMARY KEY,
column_2 VARCHAR(10) NOT NULL, column_3 VARCHAR(10) NOT NULL);
INSERT INTO table_a VALUES ('Ford', 'Blue', '4door');
INSERT INTO table_a VALUES ('Chevy', 'Blk', '2door');
INSERT INTO table_a VALUES ('Honda', 'Blue', '4door');
INSERT INTO table_a VALUES ('Nissan', 'Red', '2door');

CREATE TABLE table_b (vehicle_make VARCHAR(10) NOT NULL PRIMARY KEY);
INSERT INTO table_b VALUES ('Ford');
INSERT INTO table_b VALUES ('Chevy');
INSERT INTO table_b VALUES ('Honda');
INSERT INTO table_b VALUES ('Nissan');
INSERT INTO table_b VALUES ('Toyota');
INSERT INTO table_b VALUES ('Jeep');

SELECT B.vehicle_make, A.column_2, A.column_3
FROM table_b AS B
LEFT JOIN table_a AS A
ON B.vehicle_make = A.vehicle_make
AND column_2 = 'Blue'
AND column_3 = '4door' ;

Result:

vehicle_make column_2 column_3
------------ ---------- ----------
Chevy NULL NULL
Ford Blue 4door
Honda Blue 4door
Jeep NULL NULL
Nissan NULL NULL
Toyota NULL NULL

(6 row(s) affected)


Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]