Topic awaiting preservation: Mysql query (Page 1 of 1)

i have two tables the first table is "user" and the other is "player"

the user have to select 11 players, for every field f1,f2,f3,f4-f11, this fields contains id from player's.
inn the player table the player contains points, cost and ID.

what i want to do is, print the user with the moust points.

how would i write the sql query?

just use the SELECT ........ FROM .... ORDER BY points [ASC

i don't think that will work, cause i need one value from user and one value from player

player
--------------------
id

Okay, your database needs to be put into 3rd normal form. Read about Normalized Databases.

Now, this may not be easy to understand right away, so just ask questions and I'll clarify. Basically, in your database the way you currently have it, you have a relationship where one user can have many players and one player can be assigned to many users. This relationship is called a many-to-many relationship. In a normalized database, you only want one-to-many and one-to-one relationships. The way to solve this problem is create a two-column table comprised of the ids from the two existing tables. This is called a JUNCTION table and in this case it is necessary to give you access to the GROUPWISE functions (see below). Also, I don't know if your user table has a numeric AUTO_INCREMENT id, but i recommend it does. Here is my new recommneded table structure:

PLAYER
id

Thanks jiblet,

this is just wat i'm looking for.
i just hoap i understand it

and thanks again.

I read about 5 articles on database normalization, and none of them really explained how to model many-to-many relationships. It wasn't until I actually got into the trenches that I realized how the junction table works, and how the methods I outlined above give you the ideal flexibility when working with many-to-many relationships.

If you don't understand this now, I'm sure you will by the time you get done working with it

(btw, just for future reference, 'hope' <- correct spelling)

-jiblet

I took a semester course at college on Relational Database design, and still, normalization was a more difficult thing to grasp... The actual "what a normalized db is" part isn't that hard... i found that converting a non-normalized db to 3nf was the most difficult to learn...

good luck

--Lurch--

Hi again, i have learn much about sql lately, but i still can do what i want to do.

the problem is this,
i don't know how to get the points from the player table, more then once inn one row.

this is the result i get now from this query:
select U.user, U.f1, U.f2, P.ID, P.name, P.points from fUser as U, fPlayers as P where P.ID = U.f1 or P.ID = U.f2;

+---------+-----+-----+-----+-----------------+--------+

Well, that's a tough one indeed. There may be a way to accomplish that, but I am not aware of it. It would likely be some GROUPWISE function like ROWStoCOLUMNS() or LISTROWS().

If it was my application, I would simply let the user information repeat by dropping the GROUP BY clause. Then in my application, I would pull each player information with a loop like:

$i = 0;

while ($row = mysql_fetch_row($result)) {

  if($row['name'] != $lastName) {

    $j = 0;

    $i++;

    $lastName = $row['name'];

  }  

  $players[$i][$j] = $row['player'];

  $j++;

}

That's just the basic idea, if you need more help piecing it together maybe someone else can help you or I should have some free time SOME TIME in the next week

-jiblet

[This message has been edited by jiblet (edited 03-07-2002).]

i think yo mean this (sql statements in uppercast)

SELECT u.name, SUM(p.points) as total
FROM users AS u,
players AS p
WHERE u.f1=p.id OR u.f2=p.id OR u.f3=p.id
GROUP BY u.name
ORDER BY total DESC

as you can see the WHERE statement can get exhausting, especially if you want more than 11 later on. It is better to use a middle table. A user to player link table as jiblit suggests. that way you could use a

SELECT ...
FROM users
LEFT JOIN linktable ON userid=playerlink
LEFT JOIN players ON playerlink=playerid

(or to that extend)

i tried your second question a bit with JOIN and IN(....) statements, but
that is IMPOSSIBLE without a link table because you can only really JOIN once on a table (not 11 times).

Gerhard <tr909>


[This message has been edited by tr909 (edited 03-15-2002).]