CODEWITHSUNDEEP
mysqlwhere-in
user5534204
user5534204

Reputation:

How to use mysql WHERE IN for two separate tables

I have two tables

books_tbl:

blocks  side-bar   top-bar
 23,45   3,15      11,56

pages_tbl:

id title
1  ff
3
11
15

I want to select the rows from pages_tbl where pages id has included either blocks, side-bar or tob-bar columns in books_table.

How to process this?

Upvotes: 0

Views: 64

Answers (2)

fthiella
fthiella

Reputation: 49049

It's usually not a good idea to store comma separated values in a single field. If you really cannot change your data structure, you could use a query like this:

select p.id, p.title
from
  pages_tbl p inner join books_tbl b
  on (
    find_in_set(p.id, b.blocks)
    or find_in_set(p.id, b.side-bar)
    or find_in_set(p.id, b.top-bar)
  )
-- add where condition?
group by p.id, p.title

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You should really consider fixing your table structure. Never store multiple value in a single cell. See Normalization.

As in this case you can't, try using find_in_set function.

select
from pages_tbl p
where exists (
    select 1
    from books_tbl b
    where find_in_set(
        p.id, 
        concat(b.blocks, ',', b.side_bar, ',', b.top_bar)
        ) > 0
);

Remember though that this will be slow because the server can't use index if any.

Upvotes: 1

Related Questions