Best way to pass missing values in first query to 2nd query

[Sam]

Hi

I have a query looking up part# in a part#/part desc table
Some of the part # are missing so I want to query an older table for missing
part desc

What is the best way to do this???
Thanks

Table1 (Part #)
part1
part2
part3

Table2 (Part# and New Desc)
part1 - part1 new desc
part3 - part3 new desc

Table3(Part# and Old desc)
part1- part1 old desc
part2- part2 old desc
part3 -part3 old desc

What I want
part1- part1 new desc
part2- part2 old desc
part3 -part3 new desc

 

[peregenem]

I have a query looking up part# in a part#/part desc table
Some of the part # are missing so I want to query an older table for missing
part desc

What is the best way to do this???

You didn't advise whether the 'description' columns allow NULL values
and/or zero length strings and, if so, how to handle them. I think you
want something like my suggested solution 3 (below) but take a look at
the other two to be aware of the issues:

CREATE TABLE Table1 (
part_nbr INTEGER NOT NULL);

CREATE TABLE Table2 (
part_nbr INTEGER NOT NULL,
new_description VARCHAR(50));

CREATE TABLE Table3 (
part_nbr INTEGER NOT NULL,
old_description VARCHAR(50));

INSERT INTO Table1 VALUES (1);
INSERT INTO Table1 VALUES (2);
INSERT INTO Table1 VALUES (3);
INSERT INTO Table1 VALUES (4);
INSERT INTO Table1 VALUES (5);

INSERT INTO Table2 VALUES (1,'new desc');
INSERT INTO Table2 VALUES (3,'new desc');
INSERT INTO Table2 VALUES (4, NULL);
INSERT INTO Table2 VALUES (5, '');

INSERT INTO Table3 VALUES (1,'old desc');
INSERT INTO Table3 VALUES (2,'old desc');
INSERT INTO Table3 VALUES (3,'old desc');
INSERT INTO Table3 VALUES (4,'old desc');
INSERT INTO Table3 VALUES (5,'old desc');

-- Solution 1
SELECT Table1.part_nbr,
IIF(Table2.part_nbr IS NOT NULL, Table2.new_description,
Table3.old_description) AS description, TYPENAME(description) AS
type_name
FROM (Table1 INNER JOIN Table3 ON Table1.part_nbr = Table3.part_nbr)
LEFT JOIN Table2 ON Table1.part_nbr = Table2.part_nbr;

-- Solution 2
SELECT Table1.part_nbr,
IIF(Table2.new_description IS NOT NULL, Table2.new_description,
Table3.old_description) AS description, TYPENAME(description) AS
type_name
FROM (Table1 INNER JOIN Table3 ON Table1.part_nbr = Table3.part_nbr)
LEFT JOIN Table2 ON Table1.part_nbr = Table2.part_nbr;

-- Solution 3
SELECT Table1.part_nbr,
IIF(LEN(Table2.new_description) > 0, Table2.new_description,
Table3.old_description) AS description, TYPENAME(description) AS
type_name
FROM (Table1 INNER JOIN Table3 ON Table1.part_nbr = Table3.part_nbr)
LEFT JOIN Table2 ON Table1.part_nbr = Table2.part_nbr;