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.

 

[nicsin]

select 
  distinct product
from
  tablename
where
  buildid in ('D024','C879')

 

[lespaul]

SELECT DISTINCT A.Product FROM (SELECT Product FROM TableName WHERE BuildID IN ("D024", "C879")) As A

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]

Thanks I have another question. Will this just give me Products unique to Build ID's D024 & C879 only? How will this look to see if the Product has other Build ID's. It seems to me that this will give me Products that have D024 & C879 but I need to know that the Products only have D024 & C879 and none of the other Build ID's. I hope this is not a dumb question!

 

[nicsin]

netrusher,

eeer this will return products that have either D024 or C879. if you want products that have both change the where clause with

where buildid = 'D024' and buildid = 'C897'

lespaul,

why did you use an embedded query?

 

[netrusher]

lespaul,

What do you mean by embedded query?

Also, I am not sure if I explained my issue correctly. I want Products that have D024 or C879 but not G004, E818, N005, or F813.

Thanks for your help!

 

[nicsin]

my first post will do what you want. have you even tried the query????

 

[netrusher]

Thanks,

I have tried it but and it gives me the Products that are associated with D024 & C879 but they some of them are also associcated with G004, E818, N005 AND F813. I want the Products that are only assoicated with D024 & C879 and none of the other Build ID's.

Thanks again!

 

[lespaul]

maybe:

SELECT DISTINCT A.Product FROM (SELECT Product FROM TableName WHERE BuildID IN ("D024", "C879") GROUP BY Product HAVING Count(*) = 2) As A

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

 

[nicsin]

i see. try this

SELECT DISTINCT product
FROM tablename
WHERE product in
(
select distinct product from tablename where buildid in ('D024','C879')
)
and product not in 
(
select distinct product from tablename where buildid not in ('D024','C879')
)

 

[netrusher]

Leslie,

Thanks for your help on this.

This is still not giving me Products that are only for Build ID D024 & C879. It is giving me Products that are for D024 & C879 but many of the Products are also for G004, E818, N005, F813 also. I need the Products that are for D024 & C879 only and not any of the other Build Ids.

 

[netrusher]

nicsin,

What does the
(
)

represent?

 

[nicsin]

the general format is

select * from aTable where aField in (value1,value2,...)

in this case the values are fetched by embedded select queries.

did it work?

 

[netrusher]

Well so far it has been running for about 20 minutes and holding!

 

[netrusher]

Leslie,

Do you have any other suggestions?

 

[netrusher]

nicsin,

The query is still going after 30 minutes. Something does not seem right about that length of time???

 

[nicsin]

how big is the table???

 

[netrusher]

The table has 16658 records.

 

[nicsin]

i think the db is crashed. i ran it against a test db and it was ok. try it again

 

[netrusher]

I am running again at this time. It has been about 10 minutes so far. Below is my code:

SELECT DISTINCT product
FROM SixSevenC56
WHERE product in
(
select distinct product from SixSevenC56 where build_id in ('D024','C879')
)
and product not in 
(
select distinct product from SixSevenC56 where build_id not in ('D024','C879')
)