Adding rows from Lookup where not in Data

Question

Post reply

Adding rows from Lookup where not in Data

roger-562742

Old Hand

Points: 300
More actions
September 3, 2024 at 10:09 am

#4453546

I'm trying & failing to add all possible codes from a Codes table, where those codes are not present in the data. Here is a simplified set of data:
DROP TABLE IF EXISTS #Codes
CREATE TABLE #Codes([Standard] VARCHAR(4), Code VARCHAR(6))
INSERT INTO #Codes ([Standard],
Code)
VALUES ('STDA', 'Code1'),
('STDA', 'Code2'),
('STDA', 'Code3'),
('STDA', 'Code4'),
('STDA', 'Code5'),
('STDA', 'Code6'),
('STDA', 'Code7'),
('STDA', 'Code8'),
('STDA', 'Code9'),
('STDA', 'Code10'),
('STDA', 'Code11'),
('STDA', 'Code12'),
('STDA', 'Code13'),
('STDA', 'Code14'),
('STDA', 'Code15'),
('STDA', 'Code16'),
('STDA', 'Code17'),
('STDA', 'Code18'),
('STDA', 'Code19'),
('STDA', 'Code20'),
('STDA', 'Code20'),
('STDB', 'Code1'),
('STDB', 'Code2'),
('STDB', 'Code3'),
('STDB', 'Code4'),
('STDB', 'Code5'),
('STDB', 'Code6'),
('STDB', 'Code7'),
('STDC', 'Code1'),
('STDC', 'Code2'),
('STDC', 'Code3'),
('STDC', 'Code4'),
('STDC', 'Code5'),
('STDC', 'Code6'),
('STDC', 'TypeX'),
('STDD', 'Code1'),
('STDD', 'Code98'),
('STDD', 'Code32')

DROP TABLE IF EXISTS #Data
CREATE TABLE #Data (LineID VARCHAR(10), [Standard] VARCHAR(4), Code VARCHAR(6))
INSERT INTO #Data (LineID,
[Standard],
Code)
VALUES ('Line1', 'STDA','Code10'),
('Line1', 'STDA','Code8'),
('Line1', 'STDA','Code3'),
('Line1', 'STDA','Code9')
What I need to return is a every Code in the Codes table where the Standard column in both tables match (20 records in this case).
What I get (so far, using Left Outer/Full & Cross joins ) anything but... e.g.-
SELECT DISTINCT a.*, b.* FROM #Codes A
LEFT JOIN #Data B ON A.Standard = B.Standard
AND A.Code = B.Code
Any help hugely appreciated!
R

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

roger-562742 Old Hand Points: 300 More actions · Answer 1

... P.S. as there can be many Standards, I'm hoping not to Hard-Code WHERE clause on that column.

Phil Parkin SSC Guru Points: 246987 More actions · Answer 2

Like this? Returns 21 rows, so maybe not quite!

SELECT *
FROM #Codes c
WHERE EXISTS
(
    SELECT 1 FROM #Data d WHERE d.Standard = c.Standard
);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

Adding DISTINCT would make it 20 rows.

Why does your codes table contain duplicates?

This reply was modified 3 months, 3 weeks ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

roger-562742 Old Hand Points: 300 More actions · Answer 4

Thank you Phil

The duplicate in the Code table an error on my part - I manufactured this sample carelessly! Your solution is gracefully simple, my actual data is of course thousands of records, but the principle applies.

Thanks again.

R