LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 05-27-2004, 05:42 PM   #1
Gerardoj
Member
 
Registered: May 2003
Location: Somewhere over the Rainbow
Distribution: Slackware 9.x, Knoppix, Damn Small Linux, RedHat.
Posts: 465

Rep: Reputation: 30
SELECT on MySQL Help


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:
mysqlSELECT MisEquipos.equipoPredictionData.homescorePredictionData.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 |
+--------+-----------+-----------+

Last edited by Gerardoj; 05-27-2004 at 06:37 PM.
 
Old 05-28-2004, 03:07 PM   #2
vasudevadas
Member
 
Registered: Jul 2003
Location: Bedford, UK
Distribution: Slackware 11.0, LFS 6.1
Posts: 519

Rep: Reputation: 30
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.
 
Old 05-28-2004, 03:22 PM   #3
Hko
Senior Member
 
Registered: Aug 2002
Location: Groningen, The Netherlands
Distribution: ubuntu
Posts: 2,530

Rep: Reputation: 108Reputation: 108
Quote:
BTW - what language do you speak? I don't recognise it from your table and column names.
My bet would be on "spanish".
 
Old 05-28-2004, 04:16 PM   #4
keefaz
Senior Member
 
Registered: Mar 2004
Distribution: Slackware
Posts: 4,282

Rep: Reputation: 66
And if you just remove the "ON" from the query ?
 
Old 05-28-2004, 05:03 PM   #5
Gerardoj
Member
 
Registered: May 2003
Location: Somewhere over the Rainbow
Distribution: Slackware 9.x, Knoppix, Damn Small Linux, RedHat.
Posts: 465

Original Poster
Rep: Reputation: 30
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 |        |
+-----+-------------------+---------------------+--------------+-------------+--------+--------+

Last edited by Gerardoj; 05-28-2004 at 05:05 PM.
 
Old 05-29-2004, 02:17 AM   #6
vasudevadas
Member
 
Registered: Jul 2003
Location: Bedford, UK
Distribution: Slackware 11.0, LFS 6.1
Posts: 519

Rep: Reputation: 30
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> ...]]
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
php mysql select msound Programming 9 05-21-2005 08:44 PM
Mysql SELECT WHERE LIKE ivanatora Linux - Software 3 03-20-2005 11:43 AM
MySQL Select Count Question robgo777 Programming 9 01-07-2005 05:54 AM
mysql replicates, possible to SELECT only one per value? SerfurJ Programming 7 03-26-2004 11:35 AM
select the last x record added into mysql meluser Programming 3 04-14-2003 03:37 PM


All times are GMT -5. The time now is 09:04 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration