[RESOLVED] Selecting from database without repeats

mrchuckles2002

Hi,

I wonder if anyone can help me. I have a mysql table called fixtures, which has the rows player_id and wk1, wk2, -38.

The wk fields relate to a player_id

I want to be able to call the fixtures from say wk1 without repeating the player ids (i.e the fixtures will only be displayed once and wont list 1-v-2 and 2-v-1) So it will be something like:

Fixtures (week 1):
player_id - 1 -v- 2
player_id - 3 -v- 4
player_id - 5 -v- 6

It does not matter what is listed first, there are no home or away games.

I hope that makes sense! I cant think of another way of describing it!

Any help will be most appreciated, Thanks.

thorpe

SELECT wk1 FROM fixtures GROUP BY player_id;

mrchuckles2002

haha, is that it! will that work!?

kakki

??? dont get that, if that answer did not function, can u pls make a dump of your db ???
and then the result you want ???

mrchuckles2002

I didnt realise it was so simple.

I'll try it out and get back to you.

mrchuckles2002

I'm not sure of your response. I dont think you understood my question. I need to display a list of:

wk_id v player_id
wk_id v player_id

But each fixture is listed twice in the table. The table i use contains every player's fixtures for the whole season in each row. So rows are listed by player_id, then it contains 'wk1' 'wk2' 'wk3' 'wk4' - 'wk38'. Each Wk field contains who they are playing on that week.

So there will be two rows for every fixture.
(i.e if on wk1 player 1 is against player 2, there will be two rows one containing player_id=1, wk1=2 and another player_id=2, wk1=1. This is the same fixture but mentioned twice.

Can anyone help or is there a better way of doing it?

mrchuckles2002

Still looking for help with this. Anything will be appreciated.

kakki

what about splitting up the wk-fields in an extra-table like this
table wk:wk_id,wk( 1-38 ),playerid1,playerid2
storing the information only once(so you only get one result! )
your result would be:

select *
from player as first,wk,player as second
where first.id=wk.playerid1 and second.id=wk.playerid2

mrchuckles2002

kakki wrote:
what about splitting up the wk-fields in an extra-table like this
table wk:wk_id,wk( 1-38 ),playerid1,playerid2
storing the information only once(so you only get one result! )
your result would be:
select *
from player as first,wk,player as second
where first.id=wk.playerid1 and second.id=wk.playerid2

Yeh thanks, since posting that is what I have done. I had done it very cack handed way.

Thanks for your post.