php - Mysql 连接三个表以获取唯一数据列表

Question

我想显示 Users 表中的所有用户，其 id 在表 favorite_group_users 中显示为 user_id 对于 favorite_groups.id = favorite_group_users.id 的用户。

我使用了下面的查询，但它返回 null。

select users.id as user_id, users.first_name as name, 
        favorite_groups.id as group_id, 
        favorite_groups_users.user_id as carrier_id
from users
inner join favorite_groups
    on users.id = favorite_groups.user_id
inner join favorite_groups_users
    on favorite_groups.id = favorite_groups_users.favorite_group_id
where users.id = 38;

Answer 1

您可以尝试嵌套选择

select 
  favorite_groups.group_id, users_group.user_id
from 
  favorite_groups_users , 
  (select 
     favorite_groups_users.favorite_group_id, 
     users.user_id 
   from 
     users, favorite_groups_users 
   where 
     users.id = 38 and 
     users.id = favorite_groups.user_id
  ) users_group 
where 
   users_group.favorite_group_id=favorite_groups.group_id