MYSQL-JOIN Query return empty result

Question

hobby_users Table:

subcategory_master Table:

My Query:

SELECT hobby_users.user_subcategory_id,GROUP_CONCAT(subcategory_master.subcategory_name) AS subcategory_name
        FROM `hobby_users` 
        INNER JOIN subcategory_master
        WHERE hobby_users.hobby_users_id='1' AND subcategory_master.subcategory_master_id IN(SELECT user_subcategory_id FROM `hobby_users` WHERE hobby_users_id='1')

Actual Result:

Expected Result:

Note: The sub-query retuns 1,2

Please suggest or help for where I am wrong.

If it's not too late, you should seriously reconsider your schema, specifically the user_subcategory_id column. If it is too late, have a look at FIND_IN_SET(). — shmosel
– shmosel, Commented Feb 20, 2017 at 7:35

ScaisEdge · Accepted Answer · 2017-02-20 07:35:39Z

2

For group_concat you should use GROUP BY for grouping

SELECT 
   hobby_users.user_subcategory_id
  ,GROUP_CONCAT(subcategory_master.subcategory_name) AS subcategory_name
FROM `hobby_users` 
INNER JOIN subcategory_master
WHERE hobby_users.hobby_users_id='1' 
  AND subcategory_master.subcategory_master_id IN (SELECT user_subcategory_id FROM `hobby_users` WHERE hobby_users_id='1')
GROUP BY hobby_users.user_subcategory_id

answered Feb 20, 2017 at 7:35

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

P.Salmon · Accepted Answer · 2017-02-20 09:39:11Z

0

/*
drop table if exists hobby_users;
create table hobby_users(hobby_users_id int, user_name varchar(3), user_subcategory_id varchar(3));

drop table if exists subcategory_master;
create table subcategory_master(subcategory_master_id int, subcategory_name varchar(3));

insert into hobby_users values
(1,'abc','1,2'),
(2,'def','1,3');

insert into subcategory_master values
(1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
*/


MariaDB [sandbox]> SELECT h.hobby_users_id,
    ->    h.user_subcategory_id
    ->   ,GROUP_CONCAT(sm.subcategory_name) AS subcategory_name
    -> FROM `hobby_users` h
    -> INNER JOIN subcategory_master sm
    -> WHERE h.hobby_users_id='1'
    ->   AND
    ->   find_in_set(sm.subcategory_master_id,h.user_subcategory_id) > 0
    -> GROUP BY h.user_subcategory_id;
+----------------+---------------------+------------------+
| hobby_users_id | user_subcategory_id | subcategory_name |
+----------------+---------------------+------------------+
|              1 | 1,2                 | aaa,bbb          |
+----------------+---------------------+------------------+
1 row in set (0.00 sec)

edited Feb 20, 2017 at 9:39

answered Feb 20, 2017 at 8:46

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

4 Comments

Ramalingam Perumal Over a year ago

#1054 - Unknown column 'hobby_users.hobby_users_id' in 'where clause'

P.Salmon Over a year ago

Substitute the table name with the alias name (h)

Ramalingam Perumal Over a year ago

It will return subcategory_name as null

P.Salmon Over a year ago

Not with the information you provided. I have edited the the solution to include the hobby_users_id test and the result when the code is run.

Collectives™ on Stack Overflow

MYSQL-JOIN Query return empty result

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related