Search code examples
sqljoin

select all rows from table A with table A.status=2 and all rows from table B where A.id=B.T1_id

I want select all rows from table A with status=2 and all rows from table B which column id from table A match with column T1_id from table B

table A

id val1 val2 val3 status
1 aaa 430 40 1
2 bbb 760 45 2
3 ccc 650 90 1
4 ddd 680 30 1
5 eee 200 20 2

Table B

id T1_id TB_1 TB_2
1 1 a aa
2 2 b bb
3 2 c cc
4 2 d dd
5 3 e ee
6 4 f ff
7 5 g gg
8 5 h hh

and output is

val1 val2 val3 TB_1 TB_2
bbb 760 45 b bb
bbb 760 45 c cc
bbb 760 45 d dd
eeee 200 20 g gg
eeee 200 20 h hh
Solution

  • This is a one-to-many join issue, so you should ask yourself is this is really what you want. But if it truly is what you want to see, then it's a simple join...

    select 
 a.val1, 
 a.val2, 
 a.val3, 
 b.tb_1, 
 b.tb_2
from table_A a
join table_B b
  on a.id = b.t1_id
where a.status = 2
    val1 val2 val3 tb_1 tb_2
    bbb 760 45 b bb
    bbb 760 45 c cc
    bbb 760 45 d dd
    eee 200 20 g gg
    eee 200 20 h hh

    see fiddle