CODEWITHSUNDEEP
phpmysqlsql
user1011713
user1011713

Reputation: 279

Complicated, conditional SQL Statement

I have the following 3 tables:

team_data

id
team_id
team_name

team_members

userid
teamid

users

uid
name_f
name_l

friends

friend_id
friend_one
friend_two

When I run a query to select the logged-in user's teams, I run the following and it works perfectly:

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

When I run a query to select all of the logged-in user's friends, I run the following and it also works perfectly:

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

Now, here comes the tricky part. I am looking to select the team_id and team_name along with all of the team members that the user ($session_id) does not belong to but his or her friends belong to. I know it sounds very confusing but basically, let's say my name is Jim and I'm friends with Lisa and Patrick. I want to select the team name, team id and all of the members of every team that Lisa and Patrick have signed up with PROVIDED that I do NOT belong to those teams. If there is a team that for example, Patrick and I are both members to, that team should NOT be returned.

I have been struggling with this for the past day and any help would be greatly appreciated. Thanks. Also, apologies for not including foreign_keys...

Upvotes: 1

Views: 128

Answers (1)

GolezTrol
GolezTrol

Reputation: 116100

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)

Upvotes: 2

Related Questions