Return unique values - where item is only used 1

[tsinvr]

I am trying to figure this out, and after banging my head against the wall for a few hours I thought I would ask to see if there is something I am missing.

Scenario:
Table icfct holds transactions. A sample list of rows would look like this:
Row_ID Part_ID Program_ID
872 11-001 ICTKI
926 11-001 ICTIE
1055 11-001 SOTSE
1374 11-002 ICESS
1375 11-002 SOTSE
1971 11-002 ICUAR
2022 11-004 ICUAR

What I want to get is the part_id that ONLY has a program id of ICUAR. In this example I would only get 11-004. 11-002 would not fit the criteria because it has been used in other programs.

Anyone have any clues on where to go with this?

Thanks.

Ted

 

[donutman]

Ted, this should do it for you.

[Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]Row_ID[Gray])[/Gray][Gray],[/Gray] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]Part_ID[Gray])[/Gray][Gray],[/Gray] Program_ID
   [Blue]FROM[/Blue] YourTable [Blue]WHERE[/Blue] Program_ID[Gray]=[/Gray][red]'ICUAR'[/red]
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] Program_ID
   [Blue]HAVING[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]Program_ID[Gray])[/Gray][Gray]=[/Gray][Fuchsia]MIN[/Fuchsia][Gray]([/Gray]Program_ID[Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

BTW, that won't list all of the rows that might duplicate Program_ID AND Part_ID. You could include a COUNT(Row_ID) in the resultset to see if there were some duplicates.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]