Mysql select distinct user with each of their own preference-CodePudding

Let's say I have a user and preference table, as well as a bridge table user_preference between the two:

/* user table:  */
 ---------- -------------- 
| Field    | Type         |
 ---------- -------------- 
| id       | int          |
| username | varchar(255) |
 ---------- -------------- 

/*  preference table: */
 ------------ -------------- 
| Field      | Type         |
 ------------ -------------- 
| preference | varchar(255) |
 ------------ -------------- 

/* user_preference table:  */
 ----------------- -------------- 
| Field           | Type         |
 ----------------- -------------- 
| user_id         | int          |
| preference_name | varchar(255) |
 ----------------- --------------

For instance there are 3 preferences to choose from: "swimming", "watching TV", "cycling". And one user can have zero or all 3 of the preferences, which is reflected on the user_preference table.

Now I want to query 10 different users, and with all of them each of their own preferences included, either null or mutiple preferences, how to construct a select statement for that?

So far I have tried something like this:

SELECT u.*, p.preference_name 
FROM user u 
LEFT JOIN user_preference p ON p.user_id = u.id
LIMIT 10;

/* Result:  */
id | username | preference_name

1  | user1    | swimming
1  | user1    | cycling
2  | user2    | null
3  | user3    | watching TV
... /* rest of the result  */

As you can see the result will return a duplicate user1, and it won't be 10 distinct users. I'm aware of the distinct and group by keywords, it doesn't solve the problem, as it will only return a single preference for a user, while the user can have multiple preferences.

How to do that with one single select statement?

CodePudding user response：

Try this.

SELECT u.*, 
       GROUP_CONCAT(DISTINCT p.preference_name) AS prefs
  FROM user u 
  LEFT JOIN user_preference p ON p.user_id = u.id
 GROUP BY u.id
LIMIT 10;

The GROUP_CONCAT() will make a comma-separated list of preferences for each user.

Pro tip. When tables get very large, altering ENUMs to add more values gets very time-consuming. Plus, it's usually unwise to design a database so it needs lots of ALTER TABLE statements as it grows. So, the approach you have outlined is the right way to go if you want your possible preferences to be open-ended.