Result for first element using In statement

Question

I have three table

td_product

|--------------|------------------------------------------------------------|
| product_id   |   product_title  |     compatible_model  |                 |
|---------------------------------------------------------------------------|
|      1       |     prod-1       |       1,4,5,6,8       |                 |
|---------------------------------------------------------------------------|
|      2       |     prod-2       |       1,5,6           |                 |
|---------------------------------------------------------------------------|
|      3       |     prod-3       |       4,6             |                 |
|---------------------------------------------------------------------------|

the i have

td_model

|--------------|----------------------------------------------------|
|   model_id   |   model_title    |     brand_id  |    model_slug   |
|-------------------------------------------------------------------|
|      1       |     mode-1       |       1       |    1-mode-1     |
|-------------------------------------------------------------------|
|      2       |     mode-2       |       2       |    2-mode-2     |
|-------------------------------------------------------------------|
|      3       |     mode-3       |       4       |    3-mode-3     |
|-------------------------------------------------------------------|
|      4       |     mode-4       |       4       |    4-mode-4     |
|-------------------------------------------------------------------|
|      5       |     mode-5       |       2       |    5-mode-5     |
|-------------------------------------------------------------------|
|      6       |     mode-6       |       4       |    6-mode-6     |
|-------------------------------------------------------------------|
|      7       |     mode-7       |       1       |    7-mode-7     |
|-------------------------------------------------------------------|
|      8       |     mode-8       |       2       |    8-mode-8     |
|-------------------------------------------------------------------|

and finally the brand

|--------------|------------------------------------------------------------|
|   brand_id   |   brand_title    |                                         |
|---------------------------------------------------------------------------|
|      1       |     brnd-1       |                                         |
|---------------------------------------------------------------------------|
|      2       |     brnd-2       |                                         |
|---------------------------------------------------------------------------|
|      3       |     brnd-3       |                                         |
|---------------------------------------------------------------------------|
|      4       |     brnd-3       |                                         |
|---------------------------------------------------------------------------|
|      5       |     brnd-3       |                                         |
|---------------------------------------------------------------------------|
|      6       |     prod-3       |                                         |
|---------------------------------------------------------------------------|

Now i have a query which runs like this

SELECT * FROM td_product,td_model,td_brand
WHERE
td_product.product_id > 0
AND td_model.model_id IN (td_product.compatible_model)
AND td_model.brand_id = td_brand.brand_id
AND td_model.model_slug = '1-mode-1'

Which displays prod-1 and prod-2

but this query return null value.

SELECT * FROM td_product,td_model,td_brand
WHERE
td_product.product_id > 0
AND td_model.model_id IN (td_product.compatible_model)
AND td_model.brand_id = td_brand.brand_id
AND td_model.model_slug = '5-mode-5'

What am i doing wrong?

[Read this topic, thoroughly, 5 times, then throw away your database and start over](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). — Niels Keurentjes, Oct 23 '14 at 13:27
Oh and to concisely answer your question: "you're using comma separated values in a database". — Niels Keurentjes, Oct 23 '14 at 13:28
i cant change the database right now.. is there any other way of solution — Saswat, Oct 23 '14 at 13:46

Rimas · Accepted Answer · 2014-10-23T14:07:24.817

1

Instead of IN use FIND_IN_SET(...); change:

AND td_model.model_id IN (td_product.compatible_model)

to:

AND FIND_IN_SET(td_model.model_id, td_product.compatible_model) > 0

edited Oct 23 '14 at 14:07

answered Oct 23 '14 at 13:58

Rimas

5,904
2
26
38

Thank you, your answer was really helpful... the other guy was just giving dialogues and tried to be oversmart – Saswat Oct 23 '14 at 14:58
If 'oversmart' is your translation of 'protecting you from a performance killing construct in a fundamentally crippled database', fine. This answer may solve your immediate problem at hand but leaves the underlying problem wide open. I myself don't like giving half answers. – Niels Keurentjes Oct 23 '14 at 20:35

Result for first element using In statement

1 Answers1