Join the 3 interesting fields along with each user line

Question

I try to get 3 main interests of each user, possibly as a LEFT JOIN request.

As the application is developed, each user has a set of interests that are not "children" (rows without parent ) of the categories table.

Below are some simplified w / mock data table schemas (see SQL Fiddle demo

 -- Users table | ID | NAME | -------------- | 1 | John | | 2 | Mary | | 3 | Chris | -- Categories table -- Interests table | ID | NAME | PARENT | | ID | USER_ID | CATEGORY_ID | -------------------------------------- ------------------------------ | 1 | Web Development | (null) | | 1 | 1 | 1 | | 2 | Mobile Apps | (null) | | 2 | 1 | 1 | | 3 | Software Development | (null) | | 3 | 1 | 1 | | 4 | Marketing & Sales | (null) | | 4 | 2 | 1 | | 5 | Web Apps | 1 | | 5 | 2 | 1 | | 6 | CSS | 1 | | 6 | 3 | 1 | | 7 | iOS | 2 | | 7 | 3 | 1 | | 8 | Streaming Media | 3 | | 8 | 3 | 1 | | 9 | SEO | 4 | | 10 | SEM | 4 | 

To get the 3 main interests of this user, I usually ran this query:

 SELECT `c`.`parent` as `category_id` FROM `interests` `i` LEFT JOIN `categories` `c` ON `c`.`id` = `i`.`category_id` WHERE `i`.`user_id` = '2' GROUP BY `c`.`parent` ORDER BY count(`c`.`parent`) DESC LIMIT 3 

This query returns the top of the 3 categories (parents) of the user with id = 2

I would like to know how I can query the users table and get my 3 categories in three different fields (preferred) or as group_concat(..) in one field

 SELECT id, name, top_categories FROM users, (...) WHERE id IN ('1', '2', '3'); 

Any ideas how I should do this? Thanks!

Answer 1

First, create a tried and tested query that displays, on different lines, the three best skills for each user. Then rotate this to pull out three skills for the eah user on the right. You will need to use the expression Max (isnull (skill, '')) in the skills in each skill column.

Answer 2

This is a very rude way to do this in MYSQL to get the 3 best entries for each user.

  SELECT u.id, c.name FROM users u, categories c, (SELECT i.id, i.user_id, i.category_id, @running:=if(@previous=i.user_id,@running,0) + 1 as rId, @previous:=i.user_id FROM (SELECT * FROM intersect ORDER BY user_id) i JOIN (SELECT @running=0, @previous=0 ) r) i WHERE u.id = i.USER_ID AND i.CATEGORY_ID = c.id AND i.rId <= 3 group by u.id, c.name ; 

Hope this helps

Fiddle