mysql - MYSQL - Group Contact rows with records NOT IN

Question

My case looks simple but i'm messing around with this..

I have 4 tables: User, Macros, Categories, and another one that relate users with categories. One Macro have many Categories.

What i need, is a query that based on the Macro, get the users and the Categories where user is NOT IN.

Example: I have a macro named VEICULES, with categories CAR,TRUCK and Motorcycle. User José is on category CAR and User Julio on category CAR and TRUCK, so my query should return:

José | TRUCK,Motorcycle
Julio | Motorcycle

Tables: prd_users

 id   |  name  | Email
 ---------------------------
 1    |  José  | [e-mail]
 2    |  Júlio | [e-mail]  
 3    |  André | [e-mail]

cat_macros

 macro_id |  macro_name  
 -----------------------
 1        |  Veicules  | 

cat_categories

 category_id |  category_name | macro_id  
 ---------------------------------------
 1           |  Cars          |  1
 2           |  Trucks        |  1
 3           |  Motorcycles   |  1

prd_tr_rabbit_catg

 id |  category_id | tasker_user_id  
 ---------------------------------------
 1  |  1           |  1
 2  |  1           |  2
 3  |  2           |  2

I'm stucked on just getting the categories where the user already is ..

  SELECT prd_users.id, prd_users.name, 
  prd_users.email,cat_macros.macro_name as macro, 
  GROUP_CONCAT(cat_categories.category_name SEPARATOR ', ') as in_categories 
  FROM prd_users 
  INNER JOIN prd_tr_rabbit_catg ON prd_tr_rabbit_catg.tasker_user_id = prd_users.id
  INNER JOIN cat_categories ON cat_categories.category_id = prd_tr_rabbit_catg.category_id
  INNER JOIN cat_macros ON cat_macros.macro_id = cat_categories.macro_id
  WHERE cat_macros.macro_id = '45'
  GROUP BY prd_users.id;

标签： mysql

Answer 1

为了解决这个问题，有必要为给定的宏类别创建一个包含所有类别的所有用户的列表。这可以通过以下方式完成CROSS JOIN：

SELECT * 
FROM prd_users u
CROSS JOIN (SELECT m.macro_id, m.macro_name, c.category_name, c.category_id
            FROM cat_macros m
            JOIN cat_categories c ON c.macro_id = m.macro_id) c

然后可以将其LEFT JOIN编辑到prd_tr_rabbit_catg表中，并通过选择表中没有匹配条目的那些行prd_tr_rabbit_catg，我们可以找到给定类别没有条目的用户：

SELECT c.macro_name, u.id AS user_id, u.name, u.Email, GROUP_CONCAT(c.category_name) AS missing_cats
FROM prd_users u
CROSS JOIN (SELECT m.macro_id, m.macro_name, c.category_name, c.category_id
            FROM cat_macros m
            JOIN cat_categories c ON c.macro_id = m.macro_id) c
LEFT JOIN prd_tr_rabbit_catg x ON x.tasker_user_id = u.id AND x.category_id = c.category_id
WHERE x.id IS NULL
  AND c.macro_id = 1
GROUP BY c.macro_name, u.id

对于您的示例数据，这给出：

macro_name  user_id     name    Email               missing_cats
Veicules    1           José    [e-mail]      Motorcycles,Trucks
Veicules    2           Júlio   [e-mail]     Motorcycles
Veicules    3           André   [e-mail]     Cars,Motorcycles,Trucks

更新

要排除没有任何类别的用户，请添加一个HAVING子句：

HAVING COUNT(*) < (SELECT COUNT(*) FROM cat_categories WHERE macro_id = 1)

SQLFiddle 上的演示