three table not exists problem (was "I need help!")

mrbear · #1 (**permalink**) 02-21-05, 03:29

i got 3 tables. user , teams , upload

user contain userid & email
teams contain teamid & leaderid & gen(contain null)
upload contain teamid

leaderid = userid

The gen column must be = "gener"

I need to know the email colums of those team who is not in the upload table. HELP!!

Littlefoot · #2 (**permalink**) 02-21-05, 06:59

Would this work?

Code:

SELECT u.userid, u.email
  FROM USER u
 WHERE u.userid IN (
                   SELECT t.leaderid
                     FROM teams t
                    WHERE t.gen = 'gener'
                          AND t.teamid NOT IN (SELECT l.teamid
                                                 FROM upload l));

jay82 · #3 (**permalink**) 02-21-05, 10:56

SELECT USER.*
FROM USER
LEFT OUTER JOIN TEAM ON USER.UserId = TEAM.LeaderID
LEFT OUTER JOIN UPLOAD ON TEAM.TeamId = UPLOAD.TeamId
WHERE UPLOAD.TeamId = Null

This will show you all the users who is not in upload team
even if user is no assigned to any team

If you want to show only users which are assigned to some team and not in upload table use following query

SELECT USER.*
FROM USER
LEFT OUTER JOIN TEAM ON USER.UserId = TEAM.LeaderID
LEFT OUTER JOIN UPLOAD ON TEAM .TeamId = UPLOAD.TeamId
WHERE UPLOAD.TeamId = Null
AND TEAM.LeaderID <> Null

Hope this will work...!

Internet Services

Web Development

Digital Marketing

Consumer Tech