Mysql – Get result of joining multiple tables as one row

MySQL

I have these 2 tables:

table1:

id | name
---------
1  | john
2  | jack

table2:

id | profile_id | institution
-----------------------------
1  | 1          | SFU
2  | 1          | UBC
3  | 2          | BU
4  | 2          | USC
5  | 2          | SFU

If I want to get all the information about a user using his userid, I can simply join them using this:

select a.id, a.name, b.institution from table1 a, table2 b
where a.id = USER_ID and a.id = b.profile_id

which for USER_ID = 1 returns:

id | name | institution
-----------------------
1  | john | SFU
1  | john | UBC

What I need is actually 1 unique row instead of multiple rows. Is it in any way possible to get something like this? (I haven't seen something to do it but am not sure)

id | name | institution
-----------------------
1  | john | [SFU, UBC]

Best Answer

You can use the GROUP_CONCAT function

SELECT a.id, a.name, CONCAT('[',GROUP_CONCAT(b.institution),']') institution
FROM table1 a INNER JOIN table2 b
ON a.id = b.profile_id
WHERE a.id = USER_ID
GROUP BY a.id, a.name;

or with your original query

select a.id, a.name, CONCAT('[',GROUP_CONCAT(b.institution),']') institution
from table1 a, table2 b where a.id = USER_ID and a.id = b.profile_id
group by a.id, a.name;

Give it a Try !!!

Related Solutions

Mysql – Query performance with subquery and IN clause

Refactor the query as follows:

SELECT
    readings.*
FROM
    (
        SELECT boxsn FROM readings
        WHERE (time >= 1325404800) 
        AND (time < 1326317400) 
        ORDER BY `time` ASC
    ) readings_keys
    LEFT JOIN
    (
        SELECT id AS boxsn FROM boards WHERE siteId = '1'
    ) boards
    USING (boxsn)
    LEFT JOIN readings
    USING (boxsn)
;

Make sure you have the following indexes:

ALTER TABLE boards ADD INDEX siteId_id_ndx (siteId,id);
ALTER TABLE readings ADD INDEX time_boxsn_ndx (time,boxsn);

You can drop the other index

ALTER TABLE readings DROP INDEX boxsn_time_ndx;

You should definitely see a dramatic improvement in performance as the tables grow.

In your case,

The first EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a list of value in memory
The second EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a table.

UPDATE 2012-01-12 14:03 EDT

I refactored it again to make sure the readings keys and boards keys are combined correctly before retrieving the data from the readings table:

SELECT 
    readings.* 
FROM 
    ( 
        SELECT A.* FROM
        (
            SELECT boxsn FROM readings 
            WHERE (time >= 1325404800)  
            AND (time < 1326317400)  
            ORDER BY `time` ASC
        ) A
        LEFT JOIN
        (
            SELECT id AS boxsn
            FROM boards
            WHERE siteId = '1'
        ) B
        USING (boxsn)
        WHERE B.boxsn IS NOT NULL
    ) readings_keys 
    LEFT JOIN readings 
    USING (boxsn) 
;

Sql-server – Why aren’t primary key / foreign key matches used for joins

In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.

However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!

Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!

That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!

All this would be acceptable, if performance would be enhanced. However, that's not the case.

Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.

Therefor it is not supprising that most database vendors choose to spend their time on more important things.