SQL Question
SQL Question
(OP)
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!
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!
Talk To Other Members
RE: SQL Question
I had a similar issue which was solved in this thread:
thread183-1647402: Select header records that contain records from second table
Mark
"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
RE: SQL Question
Select part
From Table1
left join table2
on Table1.component=table2.component
Group by part
Having min(table2.component)>'
RE: SQL Question
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).