Trying to get a select statement to behave

[ghbeers]

I have a table XYZ which looks something like:

ID POS DO_THESE INQUIRY_ONLY
-- --- -------- ------------
AX 1 A A
AX 2 B C
AX 3 C E
AX 4 D
AX 5 E

AZ 1 U V
AZ 2 V
AZ 3 W

...

I have a list statement with several columns like

ID, NAME, DO_THESE, INQUIRY, ETC

So for the MAINT Column, I want all of the DO_THESE values to appear ONLY when they DO NOT appear in INQUIRY_ONLY for each If and POS.

So if ID = AX, and POS = 1 and DO_THESE is A, I DO NOT want A to appear in the DO_THESE column in that row.

When ID = AX and POS = 2 and DO_THESE is B, I DO want B to appear in the DO_THESE column in that row.

When ID = AX and POS = 3 and DO_THESE is C, I DO NOT WANT C to appear in the DO_THESE column in that row.


So on the final output I want it to appear

ID NAME DO_THESE INQUIRY_ONLY
-- ---- -------- ------------
AX SAM B A
AX SAM D C
AX SAM E

AZ SUE U V
AZ SUE W

...

So when I use EXISTS or NOT EXISTS to populate the D0_THESE column as described above, I either get all or none of them.
Have tried several approaches without success. Tried a CTE and then got duplicate rows for the other columns.

I'm hoping someone can help me find the solution to this problem.

Thank you.

 

[SkipVought]

Hi.

Please use TGML tags to FORMAT BOTH your examples. We have no idea where your [highlight #FCE94F]data values[/highlight] go; in the DO or INQ column...

ID	POS	DO_THESE	INQUIRY_ONLY
--	---	--------	------------
AX	1	A       	A
AX	2	B       	C
AX	3	C       	E
AX	4	[b][highlight #FCE94F]D[/highlight][/b]         
AX	5	[b][highlight #FCE94F]E[/highlight][/b]
AZ	1	U       	V	
AZ	2	[b][highlight #FCE94F]V[/highlight][/b]
AZ	3	[b][highlight #FCE94F]W[/highlight][/b]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]