Tricky SELECT Help Needed 2

[UncleCake]

Hi,

I have a table that contains InvNum, ItemNum, and Section. Here is an example

InvNum ItemNum Section
100 A100 1
100 B100 1

101 C100 1
101 F100 2

102 D100 2
102 D101 2

I need to write a SELECT statement that selects the InvNum of only Invoices that have the same section for all of the ItemNums.

For instance, InvNum 100 and 102 would fit the criteria. 101 would not because it has ItemNums from Section 1 and 2.

I have the SELECT and FROM part done, but I don't have the WHERE part of any additional parts that would be needed.

-Uncle Cake

 

[Wholsea]

I named your table "Orders..."

select  invnum from
(select distinct invnum, section from orders) dist_orders
group by invnum
having count(*) = 1

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[gmmastros]

This is kinda ugly and there may be a better way, but give it a shot.

Select T.InvNum
From   <TableName> T
       Inner Join (
                  Select InvNum, 
                         Count(*) As InvCount
                  From   <TableName>
                  Group By InvNum
                  ) A On T.InvNum = A.InvNum
Group By T.InvNum, T.Section, A.InvCount
Having Count(Section) = A.InvCount

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Yep, looks like I was right. There is a better way. Nice one Wholsea.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

Or even simpler

select    InvNum
from      TableA
group by  InvNum
having    count(distinct section) = 1

Regards,
AA

 

[Wholsea]

Or even simpler

CODE
select InvNum
from TableA
group by InvNum
having count(distinct section) = 1

Regards,
AA

ARGH, forgot about that syntax...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[UncleCake]

So maybe it isn't as tricky as I thought. I just realized I had to go so I will try these in the morning and hand out some stars!

Thanks for your help!

-Uncle Cake