Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: Selecting Entries from a JOIN

    I want to populate an asp select dropdown with all the people (by unique id) in a roster table that don't appear in a forms table. The two tables are joined by a relationship. Should I use a join, and if so, what is the syntax for the ON clause? For instance:

    "SELECT * FROM Roster r INNER JOIN Forms f ON ??? WHERE r.TeamID = 10"

    Or would it be easier to throw them all in the select and then take out the duplicates (I don't know how to 'remove item' in asp)?

    Thanks!!

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    So you want all of the people from roster that do not have a record in forms.

    SELECT *
    FROM Roster r
    WHERE NOT EXISTS
    (
    SELECT *
    FROM Forms f
    WHERE r.key = f.key
    )
    AND r.TeamID = 10

    Using your example of r.TeamID = 10
    MCDBA

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's a variation:
    Code:
    select r.id
         , r.name
      from Roster r 
    left outer
      join Forms f 
        on r.id = f.rosterid
     where r.TeamID = 10
       and f.rosterid is null
    rudy
    http://r937.com/

  4. #4
    Join Date
    Apr 2003
    Posts
    114
    Thank you both! I appreciate the help!!!!

Posting Permissions

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