Postgresql Query 'ANY' operator-CodePudding

I have this data where first column is called tcs_transportation code (Eg. BFKN, BOXNLW, BRNM1 etc) and the second is tcs_stages_code (e.g. OTMSC, AOTC, DOT, etc). I want to get only those transportation codes which have only PSC as its stage and none other.

"BFKN"  "OTMSC"
"BFKN"  "AOTC"
"BFKN"  "DOT"
"BFKN"  "COI"
"BFKN"  "NOTS"
"BFKN"  "PSC"
"BOXNLW"    "OTMSC"
"BOXNLW"    "NOTS"
"BOXNLW"    "AOTC"
"BOXNLW"    "PSC"
"BOXNLW"    "DOT"
"BOXNLW"    "COI"
"BRNM1" "COI"
"BRNM1" "NOTS"
"BRNM1" "DOT"
"BRNM1" "PSC"
"BRNM1" "AOTC"
"BRNM1" "OTMSC"
"CMDU120P"  "OTMSC"
"CMDU120P"  "COI"
"CMDU120P"  "DOT"
"CMDU120P"  "PSC"
"CMDU120P"  "NOTS"
"CMDU120P"  "AOTC"
"CRU8W" "OTMSC"
"CRU8W" "COI"
"CRU8W" "PSC"
"CRU8W" "NOTS"
"CRU8W" "DOT"
"CRU8W" "AOTC"
"CRU8WDETCS"    "AOTC"
"LRA"   "NOTS"
"LRA"   "DOT"
"LRA"   "COI"
"LRA"   "AOTC"
"LRA"   "PSC"
"LRA"   "OTMSC"
"LRAAC" "PSC"
"LRAAC" "AOTC"
"LRAAC" "NOTS"
"LRAAC" "OTMSC"
"LRAAC" "COI"
"LRAAC" "DOT"
"LSLRDAA"   "PSC"
"LSLRDAA"   "NOTS"
"LSLRDAA"   "AOTC"
"LSLRDAA"   "COI"
"LSLRDAA"   "OTMSC"
"LWSCNAA"   "PSC"
"LWSCNAA"   "NOTS"
"LWSCNAA"   "OTMSC"
"LWSCNAA"   "COI"
"LWSCNAA"   "AOTC"
"LWSCZAA"   "NOTS"
"LWSCZAA"   "PSC"
"LWSCZAA"   "COI"
"LWSCZAA"   "OTMSC"
"LWSCZAA"   "AOTC"
"LWSCZAA"   "DOT"
"NMGHS" "COI"
"NMGHS" "NOTS"
"NMGHS" "AOTC"
"NMGHS" "PSC"
"NMGHS" "OTMSC"
"RCRV"  "PSC"
"RCRV"  "AOTC"
"RCRV"  "COI"
"RCRV"  "NOTS"
"RCRV"  "OTMSC"
"RCRV"  "DOT"
"RRV"   "NOTS"
"RRV"   "AOTC"
"RRV"   "DOT"
"RRV"   "PSC"
"RRV"   "COI"
"RRV"   "OTMSC"
"VPU"   "NOTS"
"VPU"   "AOTC"
"VPU"   "DOT"
"VPU"   "COI"
"VPU"   "PSC"
"VPU"   "OTMSC"
"WAG10" "PSC"
"WAG10" "AOTC"
"WAG10" "COI"
"WAG10" "DOT"
"WAG10" "NOTS"
"WAG9C" "COI"
"WAG9C" "PSC"
"WAG9C" "DOT"
"WAG9C" "OTMSC"
"WAG9C" "NOTS"
"WAG9C" "AOTC"
"WDAP5" "OTMSC"
"WDAP5" "AOTC"
"WDAP5" "COI"
"WDAP5" "DOT"
"WDAP5" "NOTS"
"WDAP5" "PSC"

I have tried to get it using the query but no success:

select tcs_transportation_code from(
'QUERY TO GET THE DATA SHOWN HERE'

) as old_query where tcs_stages_code <> ANY('AOTC', 'DOT', 'NOTS', 'COI','OTMSC')

I am new to PostgreSQL. Please help with suggestions.

CodePudding user response：

You want to look at all rows of a transportation code. This means you want to aggregate your data. Then see wehther there is only one entry for the transportation code and whether this entry is PSC. One method: Check that both the MIN and the MAX value equal PSC.

select tcs_transportation_code
from ( <QUERY TO GET THE DATA SHOWN HERE> ) as old_query
group by tcs_transportation_code
having min(tcs_stages_code) = 'PSC'
   and max(tcs_stages_code) = 'PSC'
order by tcs_transportation_code;

CodePudding user response：

This may work too :

SELECT *
FROM source_table t1
WHERE NOT EXISTS(SELECT 1
                 FROM source_table t2
                 WHERE t1.transportationcode = t2.transportationcode AND stages_code != 'PSC')

CodePudding user response：

I would not use any in this case. Try this. It is longer but I hope self-explanatory.

with t as ( <QUERY TO GET THE DATA SHOWN HERE> ),
t2 as
(
 select tcs_transportation_code, array_agg(tcs_stages_code) arr
 from t
 group by tcs_transportation_code
)
select tcs_transportation_code 
from t2 
where arr = array['PSC'];

This approach is a bit more generic. You can use it to select transportation codes that have an exact set of stage codes too by parameterizing the where clause array. You may use other array operators in the where clause too.