Question 246918 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I filter for the `id`s that satisfies two different SQL database queries, where the same table is queried with both?

I have an SQL query:

select * from sval where fid = 994 and val = 'XLON'; that returns

id      fid      val
100     994      's'
102     994      'd'
103     994      'd'
104     994      'd'
110     994      's'
140     994      's'
...

And a second SQL query: select * from sval where fid = 900 and val = 'Equity'; which returns

id      fid      val
101     990      's'
102     990      'd'
110     990      'd'
120     990      'd'
140     990      'd'
159     990      's'
190     990      's'
...

I want to write a query that filters for all rows where id is in both results – how can I do this?

So from the above rows, what I want returned is:

 id      
102       
110     
140

Because the above 3 id appear in both results.

Tags:

3

Answers


  1. 0

    Sounds like you search for the IN operator + a subquery?
    https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-in/

    select * from sval where fid = 994 and val = 'XLON'
AND id IN (select id from sval where fid = 900 and val = 'Equity')
    Login or Signup to reply.
  2. 0

    You can use group by and having clauses :

    SELECT id
FROM sval 
WHERE ( fid = 994 and val = 'XLON' ) OR (fid = 900 and val = 'Equity')
GROUP BY id
having count(*) > 1;
    Login or Signup to reply.
  3. 0

    You can use INTERSECT:

    select id from sval where fid = 994 and val = 'XLON'
INTERSECT
select id from sval where fid = 900 and val = 'Equity';

    Or do a GROUP BY. Use HAVING to make sure both fid’s are there.

    SELECT id
FROM sval 
WHERE (fid = 994 and val = 'XLON')
   OR (fid = 900 and val = 'Equity')
GROUP BY id
HAVING COUNT(DISTINCT fid) > 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search