复杂的条件SQL语句

Question

我有以下3张表：

team_data

id
team_id
team_name

team_members

userid
teamid

用户

uid
name_f
name_l

之友

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 

当我运行一个查询来选择所有登录用户的朋友时，我运行以下命令，并且它也运行得很好：

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)不属于但他或她的朋友属于的所有团队成员。我知道这听起来很混乱，但基本上，假设我的名字是吉姆，我是丽莎和帕特里克的朋友。我想选择的团队名称，团队id和每个团队的成员，丽莎和帕特里克已经签署，只要我不属于这些团队。如果有一支队伍，例如，帕特里克和我都是队员，那支队伍就不应该被退回。

在过去的一天里，我一直在与此作斗争，任何帮助都将不胜感激。谢谢。另外，很抱歉没有包括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)