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

ArieAI 0
Viewed 33

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

sql postgresql
1 Answers

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.

Thorsten Kettner0
Related