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

Postgresql – SQL: select rows from a certain table based on conditions in this and another table

I have two tables that share IDs on a postgresql .
I would like to select certain rows from table A, based on condition Y (in table A) AND based on Condition Z in a different table (B) ).
For example:

Table A                  Table B
ID  |  type             ID  |  date
0      E                1      01.01.2022
1      F                2      01.01.2022 
2      E                3      01.01.2010
3      F

IDs MUST by unique – the same ID can appear only once in each table, and if the same ID is in both tables it means that both are referring to the same object.
Using an SQL query, I would like to find all cases where:
1 – the same ID exists in both tables
2 – type is F
3 – date is after 31.12.2021
And again, only rows from table A will be returned.
So the only returned row should be:1 F

Tags:

2

Answers


  1. 0

    It is a bit hard t understand what problem you are actually facing, as this is very basic SQL.

    Use EXISTS:

    select *
from a
where type = 'F'
and exists (select null from b where b.id = a.id and dt >= date '2022-01-01');

    Or IN:

    select *
from a
where type = 'F'
and id in (select id from b where dt >= date '2022-01-01');

    Or, as the IDs are unique in both tables, join:

    select a.*
from a
join b on b.id = a.id
where a.type = 'F'
and b.dt >= date '2022-01-01';

    My favorite here is the IN clause, because you want to select data from table A where conditions are met. So no join needed, just a where clause, and IN is easier to read than EXISTS.

    Login or Signup to reply.
  2. 0 
    SELECT * 
FROM A
WHERE type='F'
AND id IN (
SELECT id 
FROM B
WHERE DATE>='2022-01-01'; -- '2022' imo should be enough, need to check
);

    I don’t think joining is necessary.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search