select query help

[sir pannels]

Afternoon everybody :)
Have a problem with the following query. It is meant to list the last 5 rows of 2 tables without any conditions.
The problem I have is it only returns the very last row, and prints it 4 times. Any ideas what I have done wrong to make that happen?
I'm stumpped so any help from fresh eyes would be great.

the code ..

SELECT competition.*, user.username, timer.tleft FROM competition, user, timer ORDER BY wid DESC LIMIT 5


Cheeers :]
P

[Kid Charming]

The problem is that you don't have any conditions. Since there are no fields for the db to use to determine which rows of your competition table to join with which rows of your users table, it's joining eachrow of competition with every row of users. This is called a CROSS JOIN, and it's usually a bad idea, because it can lead to huge result sets, not to mention a log of extra rows with repeated information.

MySQL can be a little confusing on this point because of its relatively lax join syntax. Simply using the keyword JOIN is allowed, but it can mean two different things:


SELECT
t1.*
,t2.*
FROM
t1
JOIN
t2
ON
t1.id = t2.id_t1


In the above, the ON conditional tells the server that JOIN is alias of INNER JOIN -- rows from t1 are only matched to rows from t2 with an id_t1 value the same the t1's id value. Without the ON conditional, the server considers JOIN an alias of CROSS JOIN, with the results I described above.

[sir pannels]

Hi Kid,

Thanks for your reply.
Ok I did not even think twice of JOIN. Ok , well I thought I understood what you said, and came up with this ..

SELECT competition.*, user.username, user.uid FROM competition, user JOIN user ON competition.uid = user.uid ORDER BY competition.wid DESC LIMIT 5

However it does not return any results, at all but no error either. Do you see what I have done wrong or misunderstood?

Many thanks,
P

[Nischumacher]

try this
SELECT competition.*, user.username, user.uid
FROM competition
JOIN user
ON competition.uid = user.uid
ORDER BY competition.wid DESC
LIMIT 5

[sir pannels]

hey, thanks for your reply :) . However that gives me an error with the ON ... not to sure what is wrong there as I have not used ON before.

You have an error in your SQL syntax near 'ON competition.uid = user.uid ORDER BY competition.wid DESC LIMIT 5' at line 1
Any thoughts?

[Kid Charming]

Syntax-wise, Nischumacher's query should be fine. Please post your entire query exactly as it's being sent to the db.

[sir pannels]

hey kid, that was it... it was in SQLYOG... I have not writen the script for it yet, so the entire query is as above.

SQL-query :

SELECT competition.*, user.username, user.uid FROM competition JOIN user ON competition.uid = user.uid ORDER BY competition.wid DESC LIMIT 5

MySQL said:


You have an error in your SQL syntax near 'ON competition.uid = user.uid ORDER BY competition.wid DESC LIMIT 5' at line 1

cheers
P :]

[Kid Charming]

I still don't see anything wrong with that. Can you run a SHOW CREATE TABLE command on your competition and user tables and post the results?

[sir pannels]

of course :) ...
CREATE TABLE `competition` (
`wid` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL default '0',
`cid` int(11) NOT NULL default '0',
`title` varchar(150) NOT NULL default '',
`description` longtext NOT NULL,
`genre` varchar(100) NOT NULL default '',
`og` varchar(15) NOT NULL default '',
`instrument` varchar(100) NOT NULL default '',
`status` int(11) NOT NULL default '0',
`startdate` date NOT NULL default '0000-00-00',
PRIMARY KEY (`wid`)
) TYPE=MyISAM

CREATE TABLE `user` (
`uid` int(11) NOT NULL auto_increment,
`username` varchar(50) NOT NULL default '',
`points` mediumint(9) NOT NULL default '0',
`password` varchar(35) NOT NULL default '',
`email` varchar(250) NOT NULL default '',
`fullname` varchar(200) NOT NULL default '',
`gender` varchar(6) NOT NULL default '',
`country` varchar(150) NOT NULL default '',
`joindate` date NOT NULL default '0000-00-00',
`lastdate` date NOT NULL default '0000-00-00',
`ip` varchar(20) NOT NULL default '',
PRIMARY KEY (`uid`),
UNIQUE KEY `username` (`username`)
) TYPE=MyISAM

[Kid Charming]

I created those tables and ran that query through yog with no problems. What version of MySQL are you on?

[sir pannels]

MySQL 4.0.0-alpha-nt .. I will try updating and get back to you :)

[sir pannels]

yup just tried on updated mysql and it worked... sorry to waste your time guys .. but thanks very much for your help :)