×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Question

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!
 

RE: SQL Question

Try

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

RE: SQL Question

(OP)
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).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close