Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: Help with SELECT

    Hi guys help please..
    Say, i have a table that has 3 columns: participantid, participantname, recipient that recipient column contains only value from any of the participantid now im trying to figure out what would be my query string if i want to select all participantid that does not exist in the recipient column? Any help will be greatly appreciated..Thanks in advanced!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT this.participantid
         , this.participantname
         , this.recipient 
      FROM daTable AS this
    LEFT OUTER
      JOIN daTable AS that
        ON that.recipient = this.participantid 
     WHERE that.recipient IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2005
    Posts
    74
    Thanks for that..bu i solved the problem thru this sql statement
    SELECT TOP 1 participant_id
    FROM participants
    WHERE participant_id not in(select recipient from participants where recipient is not null)
    ORDER BY NEWID()
    I dont know which of our solution is much better...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    obviously, my solution is better

    your original requirement, as stated in post #1, was to "select all participantid that does not exist ..."

    the "solution" you posted returns only one of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    obviously, my solution is better
    Aren't they always?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, not always

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    no, not always


    So....let me get this straight...

    we are talking about code here....right
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    we are talking about code here....right
    i dunno about you, jack, but i was talking about SQL

    i don't do code

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    i don't do jack
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I thought it was...

    "You don't know Jack..."
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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