SELECT on MySQL Help

Gerardoj · 05-27-2004, 05:42 PM

Hi I have the following tables on MySQL DB. I dont know why currently display the same values if Rojer dont have any value on PredictionData.. I Think something is wrong on my SELECT..

Some Help?

Thanks alot.

Code:

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

| lid | username | matchdate           | hometeam | awayteam | icon1 | icon2 | homescore | awayscore | campeonatos | User |
+-----+----------+---------------------+----------+----------+-------+-------+-----------+-----------+-------------+------+
| 358 | gery     | 1969-12-31 17:59:59 | HOL      | DIN      | NULL  | NULL  |         6 |         7 |           1 | Gery |
| 357 | gery     | 2004-05-11 16:00:00 | ESP      | POR      | NULL  | NULL  |         4 |         3 |           1 | Gery |
| 356 | gery     | 2004-05-11 17:00:00 | DEN      | ESP      | NULL  | NULL  |         5 |         4 |           1 | Gery |
| 355 | gery     | 2004-05-14 13:00:00 | SUE      | NEZ      | NULL  | NULL  |         4 |         4 |           1 | Gery |
| 354 | gery     | 2004-05-18 13:00:00 | CHE      | CHI      | NULL  | NULL  |         2 |         3 |           1 | Gery |
| 352 | gery     | 2004-05-22 09:00:00 | FIN      | SUE      | NULL  | NULL  |         0 |         0 |           1 | Gery |
| 353 | gery     | 2004-05-20 13:00:00 | ING      | DEN      | NULL  | NULL  |         2 |         1 |           1 | Gery |
| 351 | gery     | 2004-05-23 13:00:00 | NOR      | ESP      | NULL  | NULL  |         2 |         2 |           1 | Gery |
| 350 | gery     | 2004-05-23 13:45:00 | ITA      | FIN      | NULL  | NULL  |         4 |         3 |           1 | Gery |
| 349 | gery     | 2004-05-25 15:00:00 | ITA      | FRA      | NULL  | NULL  |         4 |         3 |           1 | Gery |
+-----+----------+---------------------+----------+----------+-------+-------+-----------+-----------+-------------+------+

and Im executing the following SELECT

PHP Code:



mysql> SELECT MisEquipos.equipo, PredictionData.homescore, PredictionData.awayscore FROM PredictionData INNER JOIN Divisions ON  PredictionData.username = MisEquipos.username INNER JOIN Campeonatos ON PredictionData.campeonatos = Campeonatos.campeonatosmainID INNER JOIN MisEquipos ON Divisions.equipoID = MisEquipos.campoalt WHERE MisEquipos.campoalt =  33;

and the currently output is:

Code:

+--------+-----------+-----------+
| Teams | homescore | awayscore |
+--------+-----------+-----------+
| Gery   |         6 |         7 |
| Gery   |         4 |         3 |
| Gery   |         5 |         4 |
| Gery   |         4 |         4 |
| Gery   |         2 |         3 |
| Gery   |         0 |         0 |
| Gery   |         2 |         1 |
| Gery   |         2 |         2 |
| Gery   |         4 |         3 |
| Gery   |         4 |         3 |
| Rojer  |         6 |         7 |
| Rojer  |         4 |         3 |
| Rojer  |         5 |         4 |
| Rojer  |         4 |         4 |
| Rojer  |         2 |         3 |
| Rojer  |         0 |         0 |
| Rojer  |         2 |         1 |
| Rojer  |         2 |         2 |
| Rojer  |         4 |         3 |
| Rojer  |         4 |         3 |
+--------+-----------+-----------+

vasudevadas · 05-28-2004, 03:07 PM

You've shown us the data in PredictionData, but we can't answer your question if we don't know what's in MisEquipos, Campeonatos and Divisions as well.

BTW - what language do you speak? I don't recognise it from your table and column names.

Hko · 05-28-2004, 03:22 PM

Quote:

BTW - what language do you speak? I don't recognise it from your table and column names.

My bet would be on "spanish".

keefaz · 05-28-2004, 04:16 PM

And if you just remove the "ON" from the query ?

Gerardoj · 05-28-2004, 05:03 PM

Thanks for your reply's My other tables;

Code:

mysql> SELECT * FROM MisEquipos;
+-----+-------------+----------+------------+------------+----------+
| lid | campeonatos | username | equipo     | createdate | campoalt |
+-----+-------------+----------+------------+------------+----------+
|  60 |           1 | gery     | Gery       | 2004-05-28 | 33       |
|  59 |           1 | gery     | Rojer      | 2004-05-28 | 33       |
+-----+-------------+----------+------------+------------+----------+


mysql> SELECT * FROM Divisions;
+-----+----------+------------+--------------+----------+ 
| lid | equipoID | createdate | divisionname | password |
+-----+----------+------------+--------------+----------+
|  22 |       33 | 2004-05-28 | Primera      |          |
+-----+----------+------------+--------------+----------+
mysql> SELECT * FROM Campeonatos;
+-----+-------------------+---------------------+--------------+-------------+--------+--------+
| lid | campeonatosmainID | campeonat           | fechinicial  | fechfinal   | cost   | others |
+-----+-------------------+---------------------+--------------+-------------+--------+--------+
|   1 |                 1 | EuroCopa Fase 1     | 12   Julio  | 15    May   | 100.00 |        |
|   2 |                 2 | Copa America fase 1 | 12   Abril  | 15    Jul   | 125.00 |        |
+-----+-------------------+---------------------+--------------+-------------+--------+--------+

vasudevadas · 05-29-2004, 02:17 AM

OK, it's quite simple. You're joining PredictionData to MisEquipos on the username column. Since there are two rows in MisEquipos with the username "gery" this means that every row in PredictionData gets duplicated: one set of results for equipo "Gery" and the other set for equipo "Rojer".

(I'd love to know what these words mean).

Is that really what MySQL sql is like? It's wierd. The standard syntax for sql is SELECT <columns> FROM <tables> [WHERE <condition> [AND|OR <condition> ...]]