Mysql concat column on table while joining from another to php

Question

Here's the basic layout:

Table1:
    id 10, blah
    id 11, test
Table2: 
    id 1, 10, some info
    id 2, 10, more info
    id 3, 11, more info

What query using sql would allow me to display the results like so, when using php and mysql_fetch_object:

10, blah, more info, some info
11, test, more info

Right now, I have:

select * from table1
join (table2, table3)
on (table2.id = table1.id and table3.id = table1.id)

The problem with this is that I get multiple result rows for the same id because of the multiple values in tables 2 and 3.

EDIT: I got it to work!

select table1.id, group_concat(table2.data), table3.data
from table1
join (table2, table3)
on (table2.id = table1.id and table3.id = table1.id)
group by table1.id

use group_concat(), example: stackoverflow.com/questions/3894584/… — dev-null-dweller
– dev-null-dweller, Commented Oct 15, 2010 at 17:36
I got it to work as I needed from that example. Thanks!! The key point was to group_concat the column with multiple entries, and to group it by the unique id from table1. — zaryzymphony
– zaryzymphony, Commented Oct 15, 2010 at 19:09

Marc B · Accepted Answer · 2010-10-15 17:40:06Z

3

MySQL's got the group_concat function for this, though it is internally limited to a default of 1024 characters, so you can't make arbitrarily large strings with it:

SELECT table1.id, CONCAT(table1.data, GROUP_CONCAT(table2.data))
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_ID
GROUP BY table1.id

If your string ends up being "large", you might be better off pulling the data in seperate queries and doing the concatenation in PHP.

answered Oct 15, 2010 at 17:40

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

3 Comments

zaryzymphony Over a year ago

Ok, I modified mine to have the column names instead of "*", and a group_concat on the column with the multiple values. It now concats the column I want. The issue now is it is only returning one result, instead of 2. Could it be because the 2nd set doesn't contain any data in the other 2 tables?

Marc B Over a year ago

Yes, if there's no matching rows in table2, then you'll only get back to the table1.id field.

zaryzymphony Over a year ago

I need that value as well though.

Tarka · Accepted Answer · 2010-10-15 17:37:37Z

0

If you only need a few certain columns, only select those columns (rather than SELECT *), and use DISTINCT. eg: SELECT DISTINCT id, col, col FROM ...

The DISTINCT keyword will make sure only unique rows are returned. If even one value is different, however, it's not unique and another row will be returned.

answered Oct 15, 2010 at 17:37

Tarka

4,0432 gold badges25 silver badges33 bronze badges

Collectives™ on Stack Overflow

Mysql concat column on table while joining from another to php

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related