1

how can I get all usernames when I search "new1" .For eg: I should get A and B as userids 1,2 in tblC is 1,2 for row1 which has new1.What query should I use to get the above result? I really appreciate any help.Thanks in Advance.
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');

Query so far:

select * where nname="new1" from  tblC
CROSS JOIN tblB
ON tblB.userid=(SELECT userids FROM substr(tblC.userids,','))
jason
  • 3,932
  • 11
  • 52
  • 123

1 Answers1

2

You should really look at Database normalization and first normalize your structure by adding a junction table and holds a relation from tablec each relation stored in tablec will be stored in new junction table but not as comma separated list each row will hold id of c and one user id per row ,if you can't alter your schema you can use find_in_set to find values in set

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

See demo

Edit for normalize schema

I have removed userids column from your tblC and instead i have created a new junction table as tblC_user with 2 columns c_id this will related to the id column of tblC and second one userid to store user relations users for tblC see sample schema for tblC

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');

And here is your junction 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 above if you notice i haven't stored any comma separated relations each relation of user for tblC is stored in new row ,for you concerned result set i have used junction table in join also new query will be like below

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

Now above query can can be optimized by using indexes you can maintain cascading relations easily

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • Thanks a lot Sir for the above query.How Can I normalize the same 3 tables and use the query you were talking about.It would be a great help if you can show me the same .Really appreciate it. – jason Sep 01 '14 at 19:48
  • Thanks you rock.I have an additional query here.If in the above case I start typing "ne" or "new"(auto_suggestion) all fields have this text so in case of ambiguity how do I deal this problem? Sorry for the trouble but this has been worrying me. Any suggestions? – jason Sep 02 '14 at 05:22
  • 1
    @jason its better to ask a new question with all the relevant details ,also provide me the link for new question here so i will also try to look at regards – M Khalid Junaid Sep 02 '14 at 05:26
  • 1
    Thanks will do that in sometime and post a link here.Thanks again for your time. – jason Sep 02 '14 at 05:34