Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    21

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

    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!!
    Last edited by mrbear; 02-21-05 at 04:33.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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));

  3. #3
    Join Date
    Feb 2005
    Location
    London
    Posts
    19

    Faster Version

    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...!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •