More Query Help

[Kryptix]

I need to select data from 3 tables for one user. They all have a link but this query is too confusing for me.

 

The tables are:

 

rscd_players

rscd_experience

users

 

It will find the user WHERE rscd.players.user = 'hello', it then needs to find the matching result in rscd_experiences WHERE rscd_experience.user = rscd_players.user and then find the matching result in users WHERE users.id = rscd_players.owner

 

rscd_experience.user is the same as rscd_players.user

users.id is the same as rscd_experience.owner

 

I need to select the following:

 

rscd_players.user

rscd_players.username

rscd_players.combat

rscd_players.skill_total

rscd_players.group_id

rscd_players.banned

rscd_players.muted

rscd_players.login_date

rscd_players.register_date

rscd_players.register_ip

rscd_players.login_ip

rscd_experience.exp_attack

rscd_experience.exp_defense

rscd_experience.exp_strength

rscd_experience.exp_hits

rscd_experience.exp_ranged

rscd_experience.exp_prayer

rscd_experience.exp_magic

users.registered

users.username

users.group_id

 

Obviously if you show me how to select just one I'll be able to add the rest myself.

 

If you can help me with this I'll love you for ever, I really don't want to use 3 queries! 

[fenway]

Sounds like you want a simple 3-table join.

[Kryptix]

Sounds like you want a simple 3-table join.

I believe so. Could you show me how I could do that with the linking? I can select everything else and use all the WHERE clauses. Thanks.

[Kryptix]

I've been working on this and came up with:

 

SELECT * 
FROM `rscd_players`
INNER JOIN `rscd_experience`
ON `rscd_players`.`user` = `rscd_experience`.`user`
JOIN `users`
ON `rscd_players`.`owner` = `users`.`id`
WHERE `rscd_players`.`user` = '265184190476866'

 

However, what difference would INNER JOIN make compared to just JOIN and OUTER JOIN? I don't see any difference what-so-ever.

 

Is there anyway to make it faster (or less resourceful) or is that perfect (except * obviously)?

[Kryptix]

Is it worth itemizing all the columns if you're only not using maybe 10 out of 50 (ish)? With `rscd_experience` I'm using all but 2 columns. Is it worth itemizing them or should I just use *? I don't mind, it's already done, but I wanna do the quickest way.

 

Here's the current query:

 

SELECT 
`users`.`group_id`, `users`.`username`, `users`.`num_posts`, `users`.`last_post`, `users`.`registered`, `users`.`registration_ip`, `users`.`last_visit`, `users`.`sub_expires`, 
`rscd_players`.`username`, `rscd_players`.`group_id`, `rscd_players`.`owner`, `rscd_players`.`combat`, `rscd_players`.`skill_total`, `rscd_players`.`x`, `rscd_players`.`y`, `rscd_players`.`fatigue`, `rscd_players`.`combatstyle`, `rscd_players`.`block_chat`, `rscd_players`.`block_private`, `rscd_players`.`block_trade`, `rscd_players`.`block_duel`, `rscd_players`.`block_global`, `rscd_players`.`cameraauto`, `rscd_players`.`onemouse`, `rscd_players`.`soundoff`, `rscd_players`.`showroof`, `rscd_players`.`autoscreenshot`, `rscd_players`.`combatwindow`, `rscd_players`.`skulled`, `rscd_players`.`creation_date`, `rscd_players`.`creation_ip`, `rscd_players`.`login_date`, `rscd_players`.`login_ip`, `rscd_players`.`online`, `rscd_players`.`banned`, `rscd_players`.`muted`, `rscd_players`.`deaths`, `rscd_players`.`kills`, `rscd_players`.`highscores`, 
`rscd_experience`.`exp_attack`, `rscd_experience`.`exp_defense`, `rscd_experience`.`exp_strength`, `rscd_experience`.`exp_hits`, `rscd_experience`.`exp_ranged`, `rscd_experience`.`exp_prayer`, `rscd_experience`.`exp_magic`, `rscd_experience`.`exp_cooking`, `rscd_experience`.`exp_woodcut`, `rscd_experience`.`exp_fletching`, `rscd_experience`.`exp_fishing`, `rscd_experience`.`exp_firemaking`, `rscd_experience`.`exp_crafting`, `rscd_experience`.`exp_smithing`, `rscd_experience`.`exp_mining`, `rscd_experience`.`exp_herblaw`, `rscd_experience`.`exp_agility`, `rscd_experience`.`exp_thieving`
FROM `rscd_players` 
JOIN `rscd_experience` 
ON `rscd_players`.`user` = `rscd_experience`.`user` 
JOIN `users` 
ON `rscd_players`.`owner` = `users`.`id`
WHERE `rscd_players`.`username` = 'Kryptix'

 

Also, where would I put DISTINCT() so I only pull back one row in case there's a double somewhere?

 

One more thing... I have a duplicate column name in `users` as I have in `rscd_players`, they both appear as `username` when I query in PhpMyAdmin. How would I call each one when using PHP? Is it possible to use $query['user'.'username'] or $query['user.username'] to split them up or do I have to put AS 'user.group_id' (etc) after every column I select in the query?

[Kryptix]

For example:

 

SELECT 
`users`.`group_id` AS 'users.group_id', `users`.`username` AS 'users.username', `users`.`num_posts` AS 'users.num_posts', `users`.`last_post` AS 'users.last_post', `users`.`registered` AS 'users.registered', `users`.`registration_ip` AS 'users.registration_ip', `users`.`last_visit` AS 'users.last_visit', `users`.`sub_expires` AS 'users.sub_expires', 
`rscd_players`.`username` AS 'rscd_players.username', `rscd_players`.`group_id` AS 'rscd_players.group_id', `rscd_players`.`owner` AS 'rscd_players.owner', `rscd_players`.`combat` AS 'rscd_players.combat', `rscd_players`.`skill_total` AS 'rscd_players.skill_total', `rscd_players`.`x` AS 'rscd_players.x', `rscd_players`.`y` AS 'rscd_players.y', `rscd_players`.`fatigue` AS 'rscd_players.fatigue', `rscd_players`.`combatstyle` AS 'rscd_players.combatstyle', `rscd_players`.`block_chat` AS 'rscd_players.block_chat', `rscd_players`.`block_private` AS 'rscd_players.block_private', `rscd_players`.`block_trade` AS 'rscd_players.block_trade', `rscd_players`.`block_duel` AS 'rscd_players.block_duel', `rscd_players`.`block_global` AS 'rscd_players.block_global', `rscd_players`.`cameraauto` AS 'rscd_players.cameraauto', `rscd_players`.`onemouse` AS 'rscd_players.onemouse', `rscd_players`.`soundoff` AS 'rscd_players.soundoff', `rscd_players`.`showroof` AS 'rscd_players.showroof', `rscd_players`.`autoscreenshot` AS 'rscd_players.autoscreenshot', `rscd_players`.`combatwindow` AS 'rscd_players.combatwindow', `rscd_players`.`skulled` AS 'rscd_players.skulled', `rscd_players`.`creation_date` AS 'rscd_players.creation_date', `rscd_players`.`creation_ip` AS 'rscd_players.creation_ip', `rscd_players`.`login_date` AS 'rscd_players.login_date', `rscd_players`.`login_ip` AS 'rscd_players.login_ip', `rscd_players`.`online` AS 'rscd_players.online', `rscd_players`.`banned` AS 'rscd_players.banned', `rscd_players`.`muted` AS 'rscd_players.muted', `rscd_players`.`deaths` AS 'rscd_players.deaths', `rscd_players`.`kills` AS 'rscd_players.kills', `rscd_players`.`highscores` AS 'rscd_players.highscores', 
`rscd_experience`.`exp_attack` AS 'rscd_experience.exp_attack', `rscd_experience`.`exp_defense` AS 'rscd_experience.exp_defense', `rscd_experience`.`exp_strength` AS 'rscd_experience.exp_strength', `rscd_experience`.`exp_hits` AS 'rscd_experience.exp_hits', `rscd_experience`.`exp_ranged` AS 'rscd_experience.exp_ranged', `rscd_experience`.`exp_prayer` AS 'rscd_experience.exp_prayer', `rscd_experience`.`exp_magic` AS 'rscd_experience.exp_magic', `rscd_experience`.`exp_cooking` AS 'rscd_experience.exp_cooking', `rscd_experience`.`exp_woodcut` AS 'rscd_experience.exp_woodcut', `rscd_experience`.`exp_fletching` AS 'rscd_experience.exp_fletching', `rscd_experience`.`exp_fishing` AS 'rscd_experience.exp_fishing', `rscd_experience`.`exp_firemaking` AS 'rscd_experience.exp_firemaking', `rscd_experience`.`exp_crafting` AS 'rscd_experience.exp_crafting', `rscd_experience`.`exp_smithing` AS 'rscd_experience.exp_smithing', `rscd_experience`.`exp_mining` AS 'rscd_experience.exp_mining', `rscd_experience`.`exp_herblaw` AS 'rscd_experience.exp_herblaw', `rscd_experience`.`exp_thieving` AS 'rscd_experience.exp_thieving'
FROM `rscd_players` 
JOIN `rscd_experience` 
ON `rscd_players`.`user` = `rscd_experience`.`user` 
JOIN `users` 
ON `rscd_players`.`owner` = `users`.`id`
WHERE `rscd_players`.`username` = 'Kryptix'

[Kryptix]

Now taken it down to:

 

SELECT 
`u`.`group_id` AS 'u.group_id', `u`.`username` AS 'u.username', `u`.`num_posts` AS 'u.num_posts', `u`.`last_post` AS 'u.last_post', `u`.`registered` AS 'u.registered', `u`.`registration_ip` AS 'u.registration_ip', `u`.`last_visit` AS 'u.last_visit', `u`.`sub_expires` AS 'u.sub_expires', 
`p`.`username` AS 'p.username', `p`.`group_id` AS 'p.group_id', `p`.`owner` AS 'p.owner', `p`.`combat` AS 'p.combat', `p`.`skill_total` AS 'p.skill_total', `p`.`x` AS 'p.x', `p`.`y` AS 'p.y', `p`.`fatigue` AS 'p.fatigue', `p`.`combatstyle` AS 'p.combatstyle', `p`.`block_chat` AS 'p.block_chat', `p`.`block_private` AS 'p.block_private', `p`.`block_trade` AS 'p.block_trade', `p`.`block_duel` AS 'p.block_duel', `p`.`block_global` AS 'p.block_global', `p`.`cameraauto` AS 'p.cameraauto', `p`.`onemouse` AS 'p.onemouse', `p`.`soundoff` AS 'p.soundoff', `p`.`showroof` AS 'p.showroof', `p`.`autoscreenshot` AS 'p.autoscreenshot', `p`.`combatwindow` AS 'p.combatwindow', `p`.`skulled` AS 'p.skulled', `p`.`creation_date` AS 'p.creation_date', `p`.`creation_ip` AS 'p.creation_ip', `p`.`login_date` AS 'p.login_date', `p`.`login_ip` AS 'p.login_ip', `p`.`online` AS 'p.online', `p`.`banned` AS 'p.banned', `p`.`muted` AS 'p.muted', `p`.`deaths` AS 'p.deaths', `p`.`kills` AS 'p.kills', `p`.`highscores` AS 'p.highscores', 
`e`.`exp_attack` AS 'e.exp_attack', `e`.`exp_defense` AS 'e.exp_defense', `e`.`exp_strength` AS 'e.exp_strength', `e`.`exp_hits` AS 'e.exp_hits', `e`.`exp_ranged` AS 'e.exp_ranged', `e`.`exp_prayer` AS 'e.exp_prayer', `e`.`exp_magic` AS 'e.exp_magic', `e`.`exp_cooking` AS 'e.exp_cooking', `e`.`exp_woodcut` AS 'e.exp_woodcut', `e`.`exp_fletching` AS 'e.exp_fletching', `e`.`exp_fishing` AS 'e.exp_fishing', `e`.`exp_firemaking` AS 'e.exp_firemaking', `e`.`exp_crafting` AS 'e.exp_crafting', `e`.`exp_smithing` AS 'e.exp_smithing', `e`.`exp_mining` AS 'e.exp_mining', `e`.`exp_herblaw` AS 'e.exp_herblaw', `e`.`exp_thieving` AS 'e.exp_thieving'
FROM `rscd_players` AS p
JOIN `rscd_experience` AS e
ON `p`.`user` = `e`.`user`
JOIN `users` AS u
ON `p`.`owner` = `u`.`id`
WHERE `p`.`username` = 'Kryptix'

 

Is there anyway that I can get rid of all the AS in the column selects?

[fenway]

Yikes... stop posting all that code without code tags.  And give people on the boards a chance to answer first.

 

Don't alias any column that doesn't need to be different (i.e. don't collide).  The table doesn't come through into the column name.

 

INNER JOIN and "JOIN" are synonymous.

 

And DISTINCT is not a function.

[Kryptix]

Yikes... stop posting all that code without code tags.  And give people on the boards a chance to answer first.

 

Don't alias any column that doesn't need to be different (i.e. don't collide).  The table doesn't come through into the column name.

 

INNER JOIN and "JOIN" are synonymous.

 

And DISTINCT is not a function.

 

The board doesn't let you edit the post and after I had posted it I kept playing around with it and got further by myself, I obviously wouldn't want something answered that I've already done so I updated the thread. I'm sorry but what should I have done, waited until you answer a question I didn't need answering any more?

 

Does aliasing columns slow things down at all? It actually makes writing PHP a LOT easier to understand but I want the query to be as quick as possible.

[fenway]

Aliasing is all but free... though it does give the parser a bit more work to do.