I am trying to do some query in psql. but I faced this issue. I want to optimize it a little bit. An-CodePudding

SELECT year, 
(SELECT name FROM users WHERE users.id=packages.id_user_sender) as sender, 
(SELECT name FROM users WHERE users.id=packages.id_user_receiver) as receiver
FROM packages
WHERE (color='blue' or year=2015) and 
(id_user_sender, id_user_receiver) NOT IN 
(SELECT id FROM users WHERE address='Taiwan')
ORDER BY year DESC, sender DESC;

I want to do something like this but it doesn't work. so I had to write it like this.

SELECT year, 
(SELECT name FROM users WHERE users.id=packages.id_user_sender) as sender, 
(SELECT name FROM users WHERE users.id=packages.id_user_receiver) as receiver
FROM packages
WHERE (color='blue' or year=2015) and 
id_user_sender NOT IN 
(SELECT id FROM users WHERE address='Taiwan') and
id_user_receiver NOT IN 
(SELECT id FROM users WHERE address='Taiwan')
ORDER BY year DESC, sender DESC;

In short, I want to combine these lines into one. How can I do this?

id_user_sender NOT IN 
(SELECT id FROM users WHERE address='Taiwan') and
id_user_receiver NOT IN 
(SELECT id FROM users WHERE address='Taiwan')

CodePudding user response：

You can use a NOT EXISTS condition:

SELECT ...
FROM packages p
WHERE (color='blue' or year=2015) 
  and not exists (select * 
                  from users u
                  where u.address = 'Taiwan'
                    and u.id in (p.id_user_sender, p.id_user_receiver))