6 Replies Latest reply on Jul 15, 2006 3:33 PM by MikerRoo

    SQL help

    Level 7
      I need some help with a SQL statement.

      I have 2 tables in a database. One is 'team' and the other is 'users'. The
      share the common key 'username'

      What I would like is to query both tables using a join and then output only
      the username that do not appear in both lists.

      For example:

      Team: Users:
      Bob Bob
      Tammy Tammy
      Joe Joe

      The results would only be Mike's information.

      Please help!

      Wally Kolcz
      Developer / Support

        • 1. Re: SQL help
          MikerRoo Level 1
          You did not specify which DB you are using and that is very important for this kind of query.

          SQL like this will work for quality or STANDARDS COMPLIANT DB's:

          USERS U
          TEAM T ON U.UserName = T.UserName
          U.UserName IS NULL
          T.UserName IS NULL

          • 2. Re: SQL help
            draves Level 1
            If your data base supports a full outer join you might get by with one statement something like:

            select nvl(a.username,b.username) from team a full outer join users b on a.username = b.username
            where a.username is null or b.username is null

            Otherwise you will need to do something like one of the following:

            (select username from team
            select username from users)
            (select username from users
            select username from team)

            select a.username from team a left outer join users b on a.username = b.username
            where b.username is null
            select b.username from users b left outer join team a on b.username = a.username
            where a.username is null
            • 3. Re: SQL help
              Level 7
              I am using a MySQL 5 database, if that helps.

              • 4. Re: SQL help
                Level 7
                Thanks you.

                I don't know if it matters, but I just want the list from the 'users' table
                and never from the 'team' table.

                It is to allow team members that are not assigned to a project to be added
                at a different time.

                • 5. Re: SQL help
                  Level 7
                  After reading the MySQL site, will this work to only show the results in
                  table 1 that do not exist in table 2?

                  SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE
                  table2.id IS NULL

                  • 6. Re: SQL help
                    MikerRoo Level 1
                    Yes, it should.