Mysql query IN clause return results

Question

Good Day! All Fridays,

I have some problem in my sql query. I'm using IN class with subquery like this

SELECT
  cm.category_id,
  cd.name
FROM
  category_master cm,
  category_detail cd,
  brand_to_categories b2c
WHERE
  cm.category_id = b2c.category_id
  AND
  cd.category_id = cm.category_id
  AND
  cd.language_id = 1
  AND
  cm.status <> 2
  AND
  cm.category_id IN (SELECT DISTINCT sub_dd.categories FROM distribution_master bdm, distribution_detail bdd, subscription_category_to_brand_user sub_dd WHERE bdd.distribution_id = bdm.distribution_id AND bdm.distributor_id = 35 AND bdd.brand_id = 7191 AND sub_dd.sub_d_id = bdd.id)
  AND
  b2c.brand_id = 7191;

The following is the sub-query which is creating problem for me.

cm.category_id IN (
SELECT DISTINCT 
sub_dd.categories 
FROM 
distribution_master bdm, 
distribution_detail bdd, 
subscription_category_to_brand_user sub_dd 
WHERE 
bdd.distribution_id = bdm.distribution_id 
AND 
bdm.distributor_id = 35 
AND 
bdd.brand_id = 7191 
AND 
sub_dd.sub_d_id = bdd.id)

the result of the sub-query is like this.

3913,4517,6059,7137,7138,7139,7140,7141,7144

this result is coming from only single row in the target table because I stored these ids as string in the filed.

Now the problem is this, I can not get results of the all categories. Main query final result only return one category information which category_id is 3913. But if I run this query manually with sub-query values instead of the sub-query then it returns all the categories results.

Manual query with sub-query values is like this

SELECT
  cm.category_id,
  cd.name
FROM
  category_master cm,
  category_detail cd,
  brand_to_categories b2c
WHERE
  cm.category_id = b2c.category_id
  AND
  cd.category_id = cm.category_id
  AND
  cd.language_id = 1
  AND
  cm.status <> 2
  AND
  cm.category_id IN (3913,4517,6059,7137,7138,7139,7140,7141,7144)
  AND
  b2c.brand_id = 7191;

Please help me regarding this problem.

Sorry I forget, I'm using Mysql

2 problems. 1: is this mysql or sql-server ? both are very different. 2: can you please use ansi join syntax in stead of this very outdated syntax. — GuidoG
– GuidoG, Commented Feb 5, 2018 at 11:54

Tim Biegeleisen · Accepted Answer · 2018-02-05 12:02:07Z

3

Assuming you are using MySQL, use FIND_IN_SET:

WHERE
    ...
    FIND_IN_SET(cm.category_id,
                (SELECT DISTINCT sub_dd.categories
                 FROM distribution_master bdm,
                      distribution_detail bdd,
                      subscription_category_to_brand_user sub_dd
                 WHERE bdd.distribution_id = bdm.distribution_id AND
                       bdm.distributor_id = 35 AND
                       bdd.brand_id = 7191 AND
                       sub_dd.sub_d_id = bdd.id)) > 0

If you are using SQL Server, then we have to do a bit more work:

WHERE ',' + (SELECT DISTINCT ...) + ',' LIKE '%,' + cm.category_id + ',%'

General comment: Avoid storing CSV data in your SQL tables. MySQL almost made the problem worse by offering FIND_IN_SET and making it easier to skirt good table design.

edited Feb 5, 2018 at 12:02

answered Feb 5, 2018 at 11:54

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Waqas Khan Over a year ago

Sorry Sir I forget, I'm using Mysql. Thanks for your reply

Collectives™ on Stack Overflow

Mysql query IN clause return results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related