show where a component exists in several products but not all 1

[CliffordTheRedDog]

Hi Tek Tips members,
I have a temporary bill of materials table where several Product types are entered, the table is as below.
I have inserted the blank row for clarity.

Product, component, qty
A C1 2
A C2 1
A C3 2

B C1 1
B C2 1
B C4 2

C C1 2
C C2 2
C C3 2
C C4 2

1. Products may have approx 99% the same components but I would like to query to show the 1% that differs.

2. If the component is the same but the qty is different that does not need to be shown, it is only where the component is unique to one product.

3. It may help to explain if I say "do not show where a component exists in all products" or another way to put it "show where a component exists in one or more products but does not exist in all"

The query will show
A C3 2
B C4 2
C C3 2
C C4 2

Any pointers would be extemely welcome
TIA
Cliff

 

[PHV]

Something like this ?
SELECT A.Product, A.component, A.qty
FROM tblTemp AS A
WHERE (SELECT Count(*) FROM tblTemp WHERE component=A.component)
<>(SELECT Count(*) FROM (SELECT DISTINCT Product FROM tblTemp))

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

 

[CliffordTheRedDog]

PHV
That is exactly what I have asked for! You are a star. I will now edit the SQL to reflect the actual table contents
Very much obliged.
thanks
Cliff