MYSQL join comma separated query

Question

I have searched around and came up with nothing.

I have 2 tables and to not have to query the database for every post that shows i need to join them somehow.

I want to get the url from the pics table that have the id of the pics field in posts table. Now heres my problem: the pics field is a commma separated "list" (4,1 or 32,4,32,2), because every post usually have more than one picture.

Table set ups:

posts:

 id | header | text | pics
| 1     xxx     xxx    3,1     
| 2     xxx     xxx    2,10,4     
| 3     xxx     xxx    16,17,18,19     
| 4     xxx     xxx    11,12,13

pics:

id | name | url
| 1   xxx   xxx    
| 2   xxx   xxx        
| 3   xxx   xxx          
| 4   xxx   xxx          
| 10  xxx   xxx         
| 11  xxx   xxx         
| 12  xxx   xxx                  
| 13  xxx   xxx          
| 16  xxx   xxx          
| 17  xxx   xxx        
| 18  xxx   xxx

Why are you storing a comma separate list that you need to join on? Please normalize that table structure. — Taryn, Jun 04 '13 at 17:01
step 1: redesign the table so it's properly normalized. step 2) after normalization, use a plain regular JOIN query. — Marc B, Jun 04 '13 at 17:01
That `pics` CSV list could do with being a separate table, normalise the data. If pics only ever appear for a single post, adding a `post_id` key to the `pics` table would suffice. — Orbling, Jun 04 '13 at 17:02
Also there are many similar questions to this. eg. [Can you split/explode a field in a MySQL query?](http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query) [How to split comma separated text in MySQL stored procedure](http://stackoverflow.com/questions/2182668/how-to-split-comma-separated-text-in-mysql-stored-procedure), etc... — Orbling, Jun 04 '13 at 17:05

Taryn · Accepted Answer · 2013-06-04T20:20:22.640

I strongly advise that you fix your current database structure so you are not storing the data in a comma separated list. You should structure your tables similar to the following:

CREATE TABLE posts
    (`id` int, `header` varchar(3), `text` varchar(3))
;

CREATE TABLE pics
    (`id` int, `name` varchar(3), `url` varchar(3))
;

CREATE TABLE post_pics
    (`post_id` int, `pic_id` int)
;

Then you can easily get a result by joining the tables:

select p.id,
  p.header,
  p.text,
  c.name,
  c.url
from posts p
inner join post_pics pp
  on p.id = pp.post_id
inner join pics c
  on pp.pic_id = c.id;

See SQL Fiddle with demo.

If you cannot alter your table, then you should be able to query using FIND_IN_SET:

select p.id, p.header, p.text, p.pics,
  c.id c_id, c.name, c.url
from posts p
inner join pics c
  on find_in_set(c.id, p.pics)

See SQL Fiddle with Demo.

Edit, if you want the data displayed as a comma-separated list then you can use GROUP_CONCAT.

Query 1:

select p.id,
  p.header,
  p.text,
  group_concat(c.name separator ', ') name,
  group_concat(c.url separator ', ') url
from posts p
inner join post_pics pp
  on p.id = pp.post_id
inner join pics c
  on pp.pic_id = c.id
group by p.id, p.header, p.text;

See SQL Fiddle with Demo

Query 2:

select p.id, p.header, p.text, p.pics,
  group_concat(c.name separator ', ') name,
  group_concat(c.url separator ', ') url
from posts p
inner join pics c
  on find_in_set(c.id, p.pics)
group by p.id, p.header, p.text, p.pics;

See SQL Fiddle with Demo

Thank you for your quick respons, but in both cases it returns ALL (header, text, url etc) onces per picture. in your first fiddle, there is two 1's and tree 3's etc. I took your advice and structured my database as you suggested, but i need all url's to one post in ONE row. — Robin Sandström, Jun 04 '13 at 20:16
What do you want as the desired result? If you want the data to be displayed as a comma separated list, see my edit using `group_concat`. — Taryn, Jun 04 '13 at 20:17
Yes i looked around on google on it, but i dint get it to work. But your code helped me, Thanks alot my problem are solved. — Robin Sandström, Jun 04 '13 at 20:50
A quick side question to this, if there are no images the post does not show at all. Thank you for your time — Robin Sandström, Jun 04 '13 at 20:59
@RobinSandström If you want to return all posts even those without images, use a LEFT JOIN instead of the INNER JOIN. — Taryn, Jun 04 '13 at 21:00

MYSQL join comma separated query

1 Answers1

Linked

Related