-1

I have 2 tables with a 3rd mapping table that only have ids(foreign key) of the two tables. is it possible to get a result like this with just one query

╔═══════╦═══════╗
║user_id║ data  ║
╠═══════╬═══════╣
║   1   ║ {a, c}║
╠═══════╬═══════╣
║   2   ║   b   ║
╚═══════╩═══════╝

Table users:
    user_id (pk, fk to relationship.userid)

Table data:
    data_id (fk to data.data_id)
    data

Table relationship:
    user_id (fk of user.user_id)
    data_id (fk of data.data_id)

this is a sample data

USER
+-------+
|user_id|
|-------|
|   1   |
+-------+
|   2   |
+-------+

RELATIONSHIP
+-------+-------+
|user_id|data_id|
+-------+-------+
|   1   |   1   |
+-------+-------+
|   1   |   3   |
+-------+-------+
|   2   |   2   |
+-------+-------+

DATA
+-------+----+
|data_id|data|
+-------+----+
|   1   | a  |
+-------+----+
|   2   | b  |
+-------+----+
|   3   | c  |
+-------+----+

with the join query the result repeats user_id for each data it have similar to this How to get all data from 2 tables using foreign key

the problem with that is i am looping through the result in my php code to list users with all their datas once not multiple times for ever data the user have.

NeMo
  • 3
  • 2
  • Read on MySQL [`GROUP_CONCAT`](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat) and see if it works for you. – PM 77-1 Jan 29 '18 at 17:50
  • 1
    What have you tried so far???? SO is not a free coding service. – Eric Jan 29 '18 at 17:52
  • @TheOneWhoMade I typed them manually. the first one i copied it from another page. @Eric i have tried many, i posted this to know if it is even possible what i was trying to do. what im working on have more tables and relation. i am already getting kind of what i want its just that it is not returning them in one row for each user. SELECT `user`.`user_id`, `data`.`data` FROM `user` JOIN `relation` ON `user`.`user_id` = `relation`.`user_id` JOIN `data` ON `relation`.`data_id` = `data`.`data_id`. if you know, answer it. you could have said it possible or not. – NeMo Jan 29 '18 at 18:45
  • @PM77-1 thanks that worked. – NeMo Jan 29 '18 at 18:54

1 Answers1

0

You can try group_concat() :

SELECT r.user_id, group_concat(d.data)
FROM relationship r
JOIN data d ON r.data_id = d.data_id
GROUP BY r.user_id;
Zhe Chen
  • 14
  • 2
  • Thank you that actually worked! for anyone who is interested. SELECT user.user_id, group_concat(data.data) FROM user JOIN relation ON user.user_id = relation.user_id JOIN data ON relation.data_id = data.data_id group by user.user_id. – NeMo Jan 29 '18 at 18:52