Find Unique Products to Specific Build ID's 1

[netrusher]

I have a table with 5 fields:

Build ID Process Product Product_De Operation_

What I am trying to accomplish is as follows:

Each Product can be in the table mulitiple times depending on how many Build ID's it has. So if Product 123456 has Build ID's G004, E818, N005, F813, D024, C879 it will show up one time each for each Build ID. What I want to query is unique Products that are for Build ID's D024 & C879 only.

How can I accomplish this with a query. I am sure I am making this harder than it is but I sure need help.

 

[lespaul]

how about:

SELECT Distinct Product FROM SixSevenC56 WHERE BuildID = 'D024' AND Product IN (SELECT Product FROM SixSevenC56 WHERE BuildID = 'C879')

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[netrusher]

nicsin,

Your's worked. It just takes a while to run.

I appreciate your help on this.

 

[lespaul]

add an index to BuildID (from the table design view), that may help the speed.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[netrusher]

Miss Leslie,

Do I just take the table to design view, select the index icon on the tool bar and add Build Id to the index?

 

[PHV]

And what about this ?
SELECT DISTINCT A.product
FROM SixSevenC56 AS A
WHERE build_id IN ('D024','C879')
AND NOT EXISTS (SELECT * FROM SixSevenC56
WHERE product=A.product AND build_id NOT IN ('D024','C879'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886