SQL Question 1

[zandsc1]

I’m doing some querying on an MRP system, and I have two tables I’m working with in this scenario:

Table1(part,component)
Table2(component)

Table1 is basically the routing table that contains all the components for the different parts, and table2 is a certain set of components that I care about. What I need to find is all the parts in table1 where every component for that part is in table2. For example:

Table1:
(001,A)
(001,B)
(001,C)
(002,A)
(002,C)

Table2:
(A)
(C)

The result I want to return from table 2 would simply be (002). Since (001) has component (B) in its bill of materials, I don’t want to select it. The way I’ve done this so far is by finding the parts that have components that are NOT in table2, and then finding the parts that are NOT in that list:

INSERT INTO table3 SELECT part FROM table1 WHERE component NOT IN (SELECT part FROM table2);

Then

SELECT part FROM table1 WHERE part NOT IN (SELECT part FROM table3);

The problem with this is that the first query runs extremely slow. Is there a faster way to process the data, and possibly avoid creating table3?

Thanks in advance for any help!

 

[MarkSweetland]

I had a similar issue which was solved in this thread:

thread183-1647402



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[PWise]

Try

Select part
From Table1
left join table2
on Table1.component=table2.component
Group by part
Having min(table2.component)>'

 

[zandsc1]

PWise - very close to where I ended up. The left join runs significantly faster than IN/NOT IN

for the first query I ended up with:

INSERT INTO table3
SELECT part
FROM table1
LEFT JOIN table2
ON table1.component = table2.component
WHERE table2.component IS NULL

then my second query reversed the results:

SELECT part
FROM table1
LEFT JOIN table3
ON table1.part = table3.part
WHERE table3.part IS NULL

And I end up with exactly what I need, and everything runs quite quickly (compared to the NOT IN method I was using before).