Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    7

    Unanswered: Select Distinct query returning repeated rows please help i'v searched first!

    Ok I have this query

    Select Distinct b.*,g.groupname,g.user_id from betstable as b, user_id_groupname as g WHERE betCreator in (1,2,4,6,'Open') AND betBetween in (1,2,4,6,'Open')

    and it is returning :
    dateBet nameBet BetBetween|BetCreator Amount Winner Groupname
    11-24-2008 test again jon | ken 4 jon test
    11-24-2008 test again jon | ken 4 jon test2
    11-24-2008 test again jon | ken 4 jon test
    11-24-2008 test again jon | ken 4 jon test
    11-24-2008 test again jon | ken 4 jon test
    02-Apr-2008 test1 tyweed | ken 4 ken test
    02-Apr-2008 test1 tyweed | ken 4 ken test2
    02-Apr-2008 test1 tyweed | ken 4 ken test
    02-Apr-2008 test1 tyweed | ken 4 ken test
    02-Apr-2008 test1 tyweed | ken 4 ken test

    tables look like the following

    --------------Betstable------------------------------
    nameBet betBetween amount winner betId betCreator dateOfBet
    test Open 5 1 5 24-Apr-2008
    test again 2 4 4 6 4 11-24-2008
    test1 2 4 2 2 1 02-Apr-2008
    test 2 2 4 1 3 1 02-Apr-2008
    testy Open 45 4 2 02-Apr-2008
    fffffffffffffffffffffffffffffffffff 1 23456 5 2 05-Apr-2008




    --------user_id_groupname-------------------

    id user_id groupname
    1 1 test
    2 1 test2
    3 2 test
    10 4 test
    18 6 test

    ------------------------------------------

    The query is suppose to return all bets in the betstable where a user_id of betBetween,betCreator are in the same group.

    essentially only show bets where users are in the same group as the user which in this case was 1,2,4,6, open Open is a bet anyone can view. I kinda understand why it is repeating but i'm unsure how to get these same results but only showing 1 unique row for every bet. It seems to repeat every bet the number of results were found. So if six fit the query logic it repeats every find six times?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's correct

    do you know what a cross join is? that's where each row of one table is matched with every row from the other table

    so if table1 has N rows, and table2 has M rows, then the cross join will have N x M rows

    you can filter out some of these N x M rows, by having conditions in the WHERE clause

    but unless you join specific rows to specific rows, it's still a cross join

    try changing this --
    Code:
    from betstable as b, user_id_groupname as g
    to this --
    Code:
      from betstable as b
    inner
      join user_id_groupname as g
        on ...
    and put something meaningful into the ON clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2008
    Posts
    7
    thanks for trying but this did not work. It still repeated everything six times which tells me it it repeating it for every row in the table user_id_groupname. I don't know how to fix this. Here is the table again.

    id user_id groupname
    Edit Delete 1 1 test
    Edit Delete 2 1 test2
    Edit Delete 3 2 test
    Edit Delete 10 4 test
    Edit Delete 19 7 group im not in
    Edit Delete 18 6 new

    So if i do this query everything works perfect!

    Select Distinct b.* from betstable as b WHERE betCreator in ($user_id_ingroup,'Open') AND betBetween in ($user_id_ingroup,'Open')

    as soon as i add the :

    user_id_groupname as g table into the mix i get repetition. The funny thing is all i need is the darn group name of the two users are in so i can add that to the data table i'm printing which looks like below. See the only info i need from the another table is groupname to finish out the table. I'm thinking i might just use php to search the two users betbetween and betcreator to return the groupname. I konw this is alot of stuff any ideas?

    Distinct b.* from betstable as b WHERE betCreator in (1,2,4,'Open') AND betBetween in (1,2,4,'Open')
    Date Created Bet Description Bet Between Bet Amount Winner Group Name Edit
    11-24-2008 test2 jon | ken 4 jon
    02-Apr-2008 test3 tyweed | ken 4 ken
    02-Apr-2008 test4 tyweed | ken 4 tyweed
    02-Apr-2008 test5 ken | Open 45
    05-Apr-2008 test6 ken | tyweed 23456
    15-May-2008 test7 tyweed | Open 5
    15-May-2008 test8 tyweed | Open 5

Posting Permissions

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