Jon49 Jon49 - 1 month ago 7
SQL Question

Determine Non-Matching Item in Set

I have tables similar to this:

#1
OriginalID | Area
========== | ====
2 | Abdomen
3 | Abdomen

#2
Area | Part
==== | ====
Abdomen| Abdomen
Bottom | Bottom


#3
Part | OriginalID
==== | ==========
Abdomen| 2
Bottom | 3


Desired result:

OriginalID | Area | Part
========== | ==== | ====
3 | Abdomen | NULL


Once I figure out the above I can update the table to:

#3
Part | OriginalID
==== | ==========
Abdomen| 2
Bottom | 3
Abdomen| 3


So, for desired result I would like to know that it originally was assigned to 50 (table
#1
) but there is no corresponding relationship for that specific
Area
in table
#3
.

Now, I would like to find if an
Area
in
#1
is not associated to the same
ID
in
#3
.
#3
is the many to many relationship between
ID
and
Part
.
Area
has been spun out into its own table now. But it has been kept in
#1
. Unfortunately, it was still being used and now that I am refactoring some code to be correct the relationship in
#1
with
Area
hasn't been properly ported to
#2
.

#2
Is the many-to-many relationship between
Area
and
Part
.

Is there a way find which
Area
relationship hasn't been accounted for in
#3
with SQL? It would be nice to have the computer do it for me but I can't quite wrap my head around how this would be done.

Thanks in advance!

Here's Some Test Data

CREATE TABLE #1
(
OriginalID INT NOT NULL,
Area VARCHAR(50) NOT NULL
)

CREATE TABLE #2
(
Area VARCHAR(50) NOT NULL,
Part VARCHAR(50) NOT NULL
)

CREATE TABLE #3
(
Part VARCHAR(50) NOT NULL,
OriginalID INT NOT NULL
)

INSERT INTO #1 VALUES
(2, 'Abdomen'),
(3, 'Abdomen')

INSERT INTO #2 VALUES
('Abdomen', 'Abdomen'),
('Bottom', 'Bottom')

INSERT INTO #3 VALUES
('Abdomen', 2),
('Bottom', 3)

DROP TABLE #1
DROP TABLE #2
DROP TABLE #3


Jason's response is currently incorrect

SELECT a.Area
FROM #1 AS a
WHERE NOT EXISTS (
SELECT *
FROM #2 as b
INNER JOIN #3 as c
ON b.Part = c.Part
WHERE a.Area = b.Area)


Results in
NULL
result.

Answer

Below will give you which are not in #3:

SELECT a.originalID, b.part 
from #1 a
join #2 b
on a.area = b.area
where not exists (select * from #3 c where c.originalID = a.originalID and c.part = b.part)