Select where ALL items are valid on list

[ABerkowicz]

Hi - I have 2 tables, a header and a detail table. What I need to do is be able to select the header contracts when the detail table contains ALL the records in a list value. So, for example I have the following:

header table
ContractNumber
100
200
300
400

detail table
ContractNumber, ItemNumber
100,ABC1
100,TGS5
100,TEST4
100,12-222
200,ABC5
200,ABC1
200,TGS5
300,ABC1
300,TGS5
300,TEST4
300,12-222
300,Test5
400,ABC1
400,TGS5
400,TEST4
400,787878

I need to see the contractnumbers that have all of the following items:

ABC1
TGS5
TEST4
12-222

The result would be contracts 100 & 300 only

I cannot use in as it would give me all of the contracts - Ideas? Thanks!

 

[bborissov]

How about:

SELECT Detail.*
FROM Detail
INNER JOIN (SELECT ContractNumber
                   FROM Detail
                   GROUP BY ContractNumber
                   WHERE ContractNumber IN (100,300)
                   HAVING COUNT(*) > 1) Tbl1
ON Detail.ContractNumber = Tbl1.ContractNumber

not tested at all

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

SELECT H.*
FROM
   Header H
   INNER JOIN (
      SELECT ContractNumber
      FROM Detail
      WHERE ItemNumber IN ('ABC1', 'TGS5', 'TEST4', '12-222')
      GROUP BY ContractNumber HAVING Count(*) = 4
   ) X ON H.ContractNumber = X.ContractNumber

P.S. Borislav... going too fast sometimes...

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

To do this task generically insert the desired Items into a table such as #DesiredItems, join to the table instead of using IN (<list>), and put HAVING Count(*) = (SELECT Count(*) FROM #DesiredItems)

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bborissov]

Yep, I am.
But why you check for:
WHERE ItemNumber IN ('ABC1', 'TGS5', 'TEST4', '12-222')
?
What if you have:

ContractNumber, ItemNumber
100,ABC1
100,TGS5
100,TEST4
100,12-222
100,testMe
.....
300,ABC1
300,TGS5
300,TEST4
300,12-222
300,Test5
300,testMe
.....

What happens with TestMe?

Also, I don't like to involve a table in my queries when I don't needed it , if you have a proper data itegrity design you must have no orphan records in Detail table
(ESquared, no offense here, or even meant, but when I re-read my message again, I just realized that somebody could find this offensive, it's my English


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Ooops,
you are right
I missed this:
<b>
I need to see the contractnumbers that have all of the following items:

ABC1
TGS5
TEST4
12-222
</b>
Sorry.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP