postgresql - SQL: Multiple, connected filter (2 columns) -

i trying filter table specifc order ids have in 'blacklist' table. problem is, order ids unqique within country, need have connection between order id , country id. e.g. filter out order id 12345, if id occurs in us, not mexico. 'blacklist' table includes information, don't know how can filter data table appropriately, simple , statement not sufficient in case.

thanks!

daniel

edit: so, have added example @ bottom.

the expected output should

100 10000001 200 10000001 300 10000001 xx filtered out 100 10000002 xx filtered out 200 10000002 xx filtered out 300 10000002 100 10000003 200 10000003 300 10000003 100 10000004 xx filtered out 200 10000004 300 10000004 xx filtered out 

both suggested solutions helped solve problem - lot!

solution 1 http://sqlfiddle.com/#!15/b0ab6/3 solution 2 http://sqlfiddle.com/#!15/b0ab6/4

i not sure if completely, in mysql can filter pairs, e.g.

order_id  country   total_price 4523   es        6  4524   es        9  4525   fr        7  

you can query order , country this:

select * order (order_id, country)     in (select id, country blacklist country = 'es') 

hope helps.