Selecting only certain records

[LeonelSanchezJr]

My query gives me the following output:

DOCUMENT# TYPE
123 ABC
123 DEF
123 GHI
123 POD
555 ABC
555 POD

I want my final query to look like this:

DOCUMENT# TYPE
123 ABC,DEF,GHI
123 POD
555 POD

In other words, I only want to display a record for a given
document# if it has all three types "ABC,DEF,GHI" in the query. If it has less than those three document types, I do not want to show it. On the other hand, I always want to show the type "POD".

Help B-)

 

[tlbroadbent]

Try this...

Select DocNo, Type
From table1
Where Type='POD'

Union

Select Distinct DocNo, a.Type + ', ' + b.Type + ', ' + c.Type
From table1 a
Inner Join table1 b On a.DocNo=b.DocNo
Inner Join table1 c On a.DocNo=c.DocNo
Where a.Type='ABC'
And b.Type='DEF'
And c.Type='GHI'

Order by 1 Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost