Select all rows where table1.column1 = table2.column1 where at least one value table2.column2 = 1

1

I have two tables in a MySQL database

table1:
column1 column2

table2:
column1 column2

I want to select all rows where table1.column1 = table2.column1 if at least one row table2.column2 = 1

table1
12   test1
13   test3


table2
12   1
12   0
12   0
12   0
12   0
13   0
13   0
13   0
13   0

Result should be
12   1  test1
12   0  test1
12   0  test1
12   0  test1
12   0  test1
2
create data script

create table #table1
(
column1 int ,
column2 varchar(100)
)

create table #table2
(
column1 int ,
column2 int
)

insert into #table1 select 12 ,'test1'
insert into #table1 select 13 ,'test3'

insert into #table2 select 12 , 1
insert into #table2 select 12 , 0
insert into #table2 select 12 , 0
insert into #table2 select 12 , 0
insert into #table2 select 12 , 0
insert into #table2 select 13 , 0
insert into #table2 select 13 , 0
insert into #table2 select 13 , 0
insert into #table2 select 13 , 0

select * from #table2 
where 
  column1 in 
     (select column1 from #table2 where column2 = 1)

image

1 Like
3

Thank you harishgg1!
I've been struggling with this. I added an INNER JOIN to get table1.column2

SELECT * FROM table2
INNER JOIN table1
ON table1.column1 = table2.column2
WHERE
table2.column1 IN
(SELECT table2.column1 FROM table2 WHERE table2.column2 = 1)