Select query with comma and join

Question

I have tbl A and tbl B as below and I would like to get their names which is seperated by comma but I am not getting in the format I am looking for.

http://sqlfiddle.com/#!2/149093/10

User: 1,2,3 ;2 - I should also get names A,B,C and then B But I am getting only A and B. How should I get their individual names who are in the column user and also satisfy the condition that 2 at least exists.Thanks in Advance.

Expected output is :

entire row with A
entire row with B
entire row with C
entire row with A

Basically 4 rows as there are 4 users in total with their name .

|  USER | CATEGORY | USERNAME |
|-------|----------|----------|
| 1,2,3 |        1 |       A  |
| 1,2,3 |        1 |       B  |
| 1,2,3 |        1 |        C |
|     2 |        1 |        B |

whats is xpected output ? its not clear in question – Mudassir Hasan Dec 30 '14 at 10:23 — Mudassir Hasan, Dec 30 '14 at 10:23

Saharsh Shah · Accepted Answer · 2014-12-30T10:53:45.793

2

Try this:

SELECT a.user, a.category, b.username 
FROM tblA a 
JOIN tblB b ON find_in_set(b.userid,a.user) 
WHERE FIND_IN_SET(2,a.user)
ORDER BY a.user;

Check the SQL FIDDLE DEMO

OUTPUT

|  USER | CATEGORY | USERNAME |
|-------|----------|----------|
| 1,2,3 |        1 |        A |
| 1,2,3 |        1 |        B |
| 1,2,3 |        1 |        C |
|     2 |        1 |        B |

edited Dec 30 '14 at 10:53

answered Dec 30 '14 at 10:25

Saharsh Shah

28,687
8
48
83

This is really close how can I get 4 rows instead of 2 and also can you please explain the query .It would help me a ton.Thanks – jason Dec 30 '14 at 10:29
added expexted result.But can you explain the query above I tried a lot for the result you gave but I couldn't arrive at the result.Thanks – jason Dec 30 '14 at 10:34
Super +1.I have forgotten to join another table which is on created_by_sno I will update it in another question and tag you .Please look into it.Thanks again .Have a great day. – jason Dec 30 '14 at 10:48
by the way it should be A B C B .But it is ABBC .How should I get the former order? – jason Dec 30 '14 at 10:50
Awesome .I will keep you posted with updated Q .Thanks . – jason Dec 30 '14 at 10:55

score 0 · Answer 2 · answered Dec 30 '14 at 10:24

You should not use

('1,2,3', '1'),
('1,4,5', '2'),
('1,3,5', '3'),

to add multiple users to the same category. Instead, use the format you've used below that already to add users to categories:

('1', '1'),
('2', '1');

So, instead of: ('1,4,5', '2'),

Use: ('1', '2'), ('4', '5'), ('1', '2'),

Select query with comma and join

2 Answers2