0

I want to show all the data in the table b. But what I get is only 1 data, while the data I have is more than 1.

Here's table a:

id | id_category | status
---+-------------+-------
1  +  Null       +      0
2  +  Null       +      0
3  +  59,76,55   +      1

Here's table b:

 id |    name     | status
 ---+-------------+-------
  59+   data1     +      0
  76+   data2     +      0
  55+   data3     +      1

And here's the query:

select name
from table_b 
where id IN (
                select id_category 
                from table_a 
                where status = 1
            )

The data did i just get is only data1. Is there anything wrong with the query?

jutrey
  • 37
  • 6

1 Answers1

1
SELECT [DISTINCT] b.id, b.name, b.status
FROM table_b b
JOIN table_a a ON FIND_IN_SET(b.id, a.id_category)
Akina
  • 39,301
  • 5
  • 14
  • 25
  • Nice answer but `[DISTINCT]` should be `DISTINCT` only. Otherwise it will through syntax error. – Akhilesh Mishra Oct 27 '20 at 05:02
  • @AkhileshMishra *but [DISTINCT] should be DISTINCT only* - I don't know does it needed at all - so I mark it as questionable/optional. – Akina Oct 27 '20 at 05:09