sql - How to filter order numbers by multiple columns -

i have table many rows looks this:

order_id part_id    description   gl_code 12345    gk123      gun mount     6850 12345    null       freight       4050.2 12346    blac       lock          6790 12346    null       freight       4050 

i want make query returns order information part_id gk% number or blac number , order has freight gl_code of 4050.2.

the freight in description column , in different row part_id.

i not want include blac , gk% parts not have freight , 4050.2 on order.

i've been able 4050.2's or gk%'s , blac's.

one way use correlated subquery makes sure there exists row same order freight code 4050.2:

select * tab t (part_id = 'blac' or part_id 'gk%')  , exists (     select 1      tab      order_id = t.order_id        , description = 'freight'        , gl_code = '4050.2' -- remove quotes if number , not char value     ) 

with sample data return:

order_id    part_id description gl_code 12345       gk123   gun mount   6850     

another option use purely set based query:

select order_id tab (part_id = 'blac' or part_id 'gk%') intersect  select order_id tab description = 'freight' , gl_code = '4050.2' 

or if want rows freight can invert conditions:

select * shipper_line t description = 'freight' , gl_code = '4050.2'  , exists (     select 1      shipper_line      order_id = t.order_id        , (part_id = 'blac' or part_id 'gk%')  );