Query Help

[Kryptix]

Blah, I'm not sure how to do this one but I know as soon as I see the query I'll kick myself.

 

I have two tables, 'experience' and 'player'

 

I need to select the columns 'id' and 'attack' from the first table 'experience'

but ONLY if the 'player' table has the column 'on' set to '0' where the 'id' of 'experience' matches the 'id' of 'player'

 

Does that make sense?

 

I'm basically making a highscore list and I need to select the top 20 from 'experience' but only if the player had 'on' set to '0' in his 'player' table where all the settings are stored.

 

Any help would be good. I'm stuck.

[bowett]

Could you post your table CREATE statements? That way we can more easily visualise what you are trying to do.

[cags]

SELECT id, attack FROM experience 
JOIN player ON experience.id=player.id
WHERE player.on=0

[Kryptix]

SELECT id, attack FROM experience 
JOIN player ON experience.id=player.id
WHERE player.on=0

 

Thanks cags, this is what I have now:

 

SELECT `user`, `exp_attack` FROM `rscd_experience`
JOIN `rscd_players` ON `rscd_experience`.`user` = `rscd_players`.`user`
WHERE `rscd_players`.`highscores` = '0'

 

#1052 - Column 'user' in field list is ambiguous

 

How do I get around that? Also, how would I also select 'username' and 'owner' from 'rscd_players' from that same record that 'highscores' is in within the same query?

[cags]

You probably have to specify the table you wish to take it from, I know it's the same in both tables in this instance so just pick one at random.

 

SELECT rscd_experience.user, exp_attack ... etc.

[Kryptix]

Thanks mate. This is the query now:

 

SELECT `rscd_players`.`username`, `rscd_players`.`owner`, `rscd_players`.`user`, `rscd_experience`.`exp_attack` 
FROM `rscd_experience` JOIN `rscd_players` 
ON `rscd_experience`.`user` = `rscd_players`.`user` 
WHERE `rscd_players`.`highscores` = '0' 
ORDER BY `rscd_experience`.`exp_attack` 
DESC LIMIT 0, 100

 

This is perfect but it's taking 14 seconds to do the query on localhost. There's 10,000 entries in rscd_players and 10,000 entries in rscd_experience

[cags]

Are the user fields set up as indexes?

[Kryptix]

Are the user fields set up as indexes?

 

In all honesty, I'm not sure what that means. I'm pretty new to MySQL. How would I tell and what does 'indexes' do?

[Kryptix]

Solved. It was just a ADD INDEX.

[Kryptix]

Here's what your help produced: http://www.rscemulation.net/highscores.html?highscores=skill_total

 

Thanks!