Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: Help me getting a unique combination from table

    Hi, I have following table

    BetId GameId
    ==== ======
    500 108
    500 109
    501 108
    501 109
    501 110
    502 108
    502 109

    My query would have the form: select BetId where GameId in(108,109)
    from Bets
    then it has to get me BetId : 500 and 502.
    Not 501,since this is different combination(108,109,110)

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    drop table test
    create table test(BetId int,GameId int)
    go
    insert test values(500,108)
    insert test values(501,109)
    insert test values(501,108)
    insert test values(501,110)
    insert test values(502,108)
    insert test values(502,109)
    go
    select BetId
    from test
    where GameId in(108,109)
    group by BetId having count(*)=2

  3. #3
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi there.

    think this would do it (if you know how many GameIDs you expect):

    Code:
    select b.BetID
    from Bets b
         ,(select distinct BetID
           from   Bets
           where  GameID in (108, 109) ) as a
    where b.BetID = a.BetID
    group by b.BetID 
    having count(*) = 2
    Greetings,
    Carsten
    Last edited by CarstenK; 06-18-04 at 14:22. Reason: Correction of code

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So what you want is a list of betId values that are used in a set of gameId values, but only in that set of gameId values, right? IF I understand that correctly, then I'd use:
    Code:
    SELECT a.betId
       FROM Bets AS a
       WHERE  2 = (SELECT Count(*)
          FROM Bets AS b
          WHERE  b.betId = a.betId
             AND b.gameId IN (108, 109))
       GROUP BY a.betId
       HAVING 2 = Count(*)
    -PatP

Posting Permissions

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