MySQL – SQL JOIN table, 1 of which has a comma

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
user varchar(255),
 category int(255),
 PRIMARY KEY (id) 
);

CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
username varchar(255),
 userid int(255),
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblC
(
id int(11 ) NOT NULL auto_increment ,
nname varchar(255),
 userids varchar(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (user, category) VALUES
('1', '1'),
('1', '2'),
('1', '3 '),
('1', '1'),
('2', '1'),
('2', '1'),
( '2', '1'),
('2', '1'),
('3', '1'),
('2', '1') ,
('4', '1'),
('4', '1'),
('2', '1');


INSERT INTO tblB (userid, username) VALUES
('1','A'),
('2','B'),
('3' ,'C'),
('4','D' ),
('5','E');


INSERT INTO tblC (id, nname,userids) VALUES
('1','new1 ','1,2'),
('2','new2','1,3'),
('3','new3','1,4'),< br />('4','new4','3,2'),
('5','new5','5,2');

select * where nname="new1" from tblC
CROSS JOIN tblB
ON tblB.userid=(SELECT userids FROM substr(tblC.userids ,','))

select * 
from tblC c
JOIN tblB b
ON (find_in_set(b.userid,c.userids)> 0)
where c.nname="new1"

CREATE TABLE if not exists tblC
(
id int( 11) NOT NULL auto_increment ,
nname varchar(255),
 PRIMARY KEY (id)
);

INSERT INTO tblC (id, nname) VALUES
(' 1','new1'),
('2','new2'),
('3','new3'),
('4','new4'), 
('5','new5');

CREATE TABLE if not exists tblC_user< br />(
 c_id int,
 userid int
);

INSERT INTO tblC_user (c_id,userid) VALUES
('1',' 1'),
('1','2'),
('2','1'),
('2','3'),
 ('3','1'),
('3','4'),
('4','3'),
('4','2' ),
('5','5'),
('5','2');

select * 
from tblC c
join tblC_user cu on(c.id = cu.c_id)
join tblB b on (b.userid = cu.userid)
where c .nname="new1"

When I search for “new1”, how to get all usernames. For example: I should get A and B as user IDs 1,2 in tblC is 1,2 for row1 There are new 1. What query should I use to get the above results?
I really appreciate any help. Thank you.
http://sqlfiddle.com/#!2/1ab8e/2

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
user varchar(255),
 category int(255),
 PRIMARY KEY (id) 
);

CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
username varchar(255),
 userid int(255),
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblC
(
id int(11 ) NOT NULL auto_increment ,
nname varchar(255),
 userids varchar(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (user, category) VALUES
('1', '1'),
('1', '2'),
('1', '3 '),
('1', '1'),
('2', '1'),
('2', '1'),
( '2', '1'),
('2', '1'),
('3', '1'),
('2', '1') ,
('4', '1'),
('4', '1'),
('2', '1');


INSERT INTO tblB (userid, username) VALUES
('1','A'),
('2','B'),
('3' ,'C'),
('4','D'),
('5','E');


INSERT INTO tblC (id, nname,userids) VALUES
('1','new1','1 ,2'),
('2','new2','1,3'),
('3','new3','1,4'),
( '4','new4','3,2'),
('5','new5','5,2');

The query so far:

select * where nname="new1" from tblC
CROSS JOIN tblB
ON tblB.userid=(SELECT userids FROM substr(tblC.userids,',' ))

You should really check the Database normalization and first normalize your structure by adding a junction table, and save the table Relations, each relationship stored in tablec will be stored in the new junction table, but it cannot be used as a comma-separated list. Each row will contain c and a user id. Each row id. If the schema cannot be changed, you can use find_in_set to find set

select * 
from tblC c
JOIN tblB b
ON (find_in_set(b.userid,c. userids)> 0)
where c.nname="new1"

See demo

Edit normalized schema

I have deleted the userids column from tblC, but created a new junction table, because tblC_user has 2 columns c_id which will be related to the id column of tblC, and the second userid is used to store the user relationship of tblC. See tblC’s Example pattern

CREATE TABLE if not exists tblC
(
id int(11) NOT NULL auto_increment ,
nname varchar(255), 
 PRIMARY KEY (id)
);

INSERT INTO tblC (id, nname) VALUES
('1','new1'),
('2','new2'),< br />('3','new3'),
('4','new4'),
('5','new5');

This is Your join table as tblC_user

CREATE TABLE if not exists tblC_user
(
 c_id int,
 userid int
); 

INSERT INTO tblC_user (c_id,userid) VALUES
('1','1'),
('1','2'),
( '2','1'),
('2','3'),
('3','1'),
('3','4') ,
('4','3'),
('4','2'),
('5','5'),
('5 ','2');

In the above, if you notice that I did not store any comma-separated relationships, each relationship of tblC users is stored in a new row, and for the result set you are following, I am in The new query that has used the join table in the join will be as follows

select * 
from tblC c
join tblC_user cu on(c.id = cu.c_id)
join tblB b on (b.userid = cu.userid)
where c.nname="new1"

Demo 2< /p>

Now the above query can be optimized by using the index, you can easily maintain the cascading relationship