Jump to content

One for ye gurus


TEENFRONT

Recommended Posts

[quote author=TEENFRONT link=topic=111119.msg450114#msg450114 date=1160506717]
How do work with and retreive data from 2 tables ( how to i retrieve, order, etc ) 

any tuts about?
[/quote]

do the tables relate in any way, or is it just two random tables? :D
Link to comment
Share on other sites

oooooooooooooh

il try it straight away... il let you know my progress.

but heres another question first.... needs some explaining..

i have the 2 tables... they have colums "played" and "won" among others.

i want to order it by the players rating, BUT i dont work out the rating untill i have got the played and won values. get my point?

so...simply put..

sql query pulls username, played, won.
i then figure out the players rating ( a ratio of the 2 values) it returns 1,2,3,4 or 5.
i THEN want to show only the top 10 players with a 5 rating.. how on earth do i do that?

if you need me to explain a little further, i will :)

Link to comment
Share on other sites

alright, this sounds like a job for the CASE operator.  it works much like a switch structure:

[code]SELECT
  table1.something AS something,
  table2.played AS played,
  table2.won AS won,
  ROUND((5*won)/played) AS calculated_rating,
  CASE WHEN played < 10 THEN 0 WHEN calculated_rating > 5 THEN 5 ELSE calculated rating END AS actual_rating
FROM table1, table2
WHERE table1.linking_column=table2.linking_column
ORDER BY actual_rating DESC, table1.something_else ASC[/code]

some MySQL gurus out there might optimize it, but that SHOULD do what you're after.  it seems a little daunting, but hopefully the spacing and linebreaks help make it clearer.  look up the manual entry for CASE structures (in the functions and operators chapter i think), and give that a whirl.

i should note that i've simplified the "actual" rating determination a little bit.  it should reduce anyone with less than 10 games under their belt to 0, and should simplify anyone with a rating greater than 5 to 5.  however, the rating will never be negative (unless you've got a negative played or won number, which you theoretically shouldn't, unless they REALLY suck) so there's no need to have the <= 0 clause in there.  every other rating (between 0 and 5) is kosher to send back directly.
Link to comment
Share on other sites

iv just thought... maybe theres an easier way...

ok, so columns username, played and won in table1, table2 and table3 ( if you look up you see i need to pull info from multiple tables..table1,table2,table3 all 3 tables have username,played and won columns)

i want to displayed top 10 players. so if username has played 700 and won 700, hes the best. so hes number 1. if username2 has played 650 and won 649, hes number 2 etc. i can forget the rating.

How do i do a query that works that out for me?
Link to comment
Share on other sites

as far as i can tell, you'd want to do something like this:

[code]SELECT table.username AS username, table.played AS played, table.won AS won, ROUND(won / played, 2) AS win_ratio FROM tables_used WHERE stuff ORDER BY win_ratio DESC, played DESC, username ASC[/code]

this will grab all users with the highest win ratio first, and in the case of matching win ratios, will choose the one with the highest number of games played first (since i assume seniority ranks).  then it'll sort by username.  this may be oversimplified since it's easy to have a 1.00 win ratio when you're 1 for 1, so you may want to weight it by the number of games played somehow - nothing comes to me off the top of my head (i haven't done stats for a bit), but i'm sure a helpful statistician will pop up soon enough.

[b]EDIT:  after some thought, unless you normalize all your ratios (that is, turn it all into a gaussian), i think the only way to ensure a relatively fair comparison of win ratios is to add a WHERE clause that makes sure the users have played more than x games.[/b]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.