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?

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 --to this --and put something meaningful into the ON clause

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