php - 复杂的条件 SQL 语句

Question

我有以下 3 个表:

团队数据

id
team_id
team_name

团队成员

userid
teamid

用户

uid
name_f
name_l

friend

friend_id
friend_one
friend_two

当我运行一个查询来选择登录用户的团队时，我运行以下命令并且它运行良好:

SELECT t.team_id, 
       t.team_name 
FROM   team_data t, 
       team_members m 
WHERE  t.team_id = m.teamid 
       AND m.userid = $session_id 
ORDER  BY t.team_id DESC 

当我运行查询以选择所有已登录用户的 friend 时，我运行以下命令并且它也运行良好:

SELECT a.name_f, 
       a.uid, 
       a.name_l, 
FROM   users a, 
       friends b 
WHERE  a.uid = b.friend_two 
       AND b.friend_one = $session_id 
ORDER  BY b.friend_id DESC 

现在，棘手的部分来了。我希望选择 team_id 和 team_name 以及用户($session_id)不属于但他或她的 friend 属于的所有团队成员。我知道这听起来很困惑，但基本上，假设我叫吉姆，我是丽莎和帕特里克的 friend 。我想选择团队名称、团队 ID 以及 Lisa 和 Patrick 签约的每个团队的所有成员，前提是我不属于这些团队。如果有一个团队，例如，Patrick 和我都是成员，则不应返回该团队。

过去一天我一直在为此苦苦挣扎，我们将不胜感激任何帮助。谢谢。另外，很抱歉没有包括 foreign_keys...

Answer 1

select distinct
  tm.*, /* The teams */
  tma.* /* The members of those teams */
from
  friends f /* My friends */
  inner join team_members tm on tm.userid = f.friend_two /* Teams of my friends */
  inner join team_data td on td.teamid = tm.teamid /* Team data of those teams */
  /* You'll need to join team_member again to get all members of these teams */
  inner join team_members tma on tma.teamid = tm.teamid /* Members of those teams. */
where
  f.friend_one = $session_id and
  /* Exclude those teams that I'm already member of */
  not exists (
    select 
      'x' /* Constant value might speed up thing marginally */
    from 
      team_members tmm 
    where 
      tmm.teamid = tm.teamid and 
      tmm.userid = f.friend_one)