Sam said:
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;
