CODEWITHSUNDEEP
phpmysqlsqlleft-join
heradcvb
heradcvb

Reputation: 33

SQL Left Join - same results multiple times

I have the following tables:

users

id | name

info

id | info | user_id

(user_id from table info is foreign key => connects to id from table users)

It is possible that the user will have many entries in the info table (same user_id in many rows).

Now I want to fetch data and display it like that:

username:

... info ....

Example:

admin:

aaa

bbb

ccc

someuser:

ddd

eee

fff

So I use LEFT JOIN like that:

SELECT users.name, info.info
FROM users
LEFT JOIN info
ON users.id = info.user_id

But what I get is the following:

admin:

aaa

admin:

bbb

admin:

ccc

How can i display the username only once? I've tried using DISTINCT keyword after SELECT but it didn't help. Meanwhile i solve this problem inside the php code itself, but is there any way to fix this only inside the sql?

Upvotes: 3

Views: 2281

Answers (2)

Johan
Johan

Reputation: 76557

SELECT users.name, GROUP_CONCAT(info.info SEPARATOR 'whateveryouwant') as info
FROM users 
INNER JOIN info ON (users.id = info.user_id)
GROUP BY users.name

By default group_concat uses a , as separator, but you can change that if needed.

Link:
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

Upvotes: 4

RiaD
RiaD

Reputation: 47619

$lastname="";
$res=mysql_query($yourQuery);
while($row=mysql_fetch_assoc($res)){
    if($row['name']!=$lastname){
        $lastname=$row['name'];
        print $lastname.':'; // first time print it
    }
    print $row['info'];
}

Upvotes: 1

Related Questions