mySQL query

Peter B

I have a 2 tables, one a matches table and one a teams table.

Matches

• team_a
• team_b

Teams

• team_id
• team_name

team_a and team_b are both integers and correspond to the team_id.

When calling the match information I am using this statment.

"select *,
(
select team_name from teams where team_is = matches.team_a
) as team_a_name,
(
select team_name from teams where team_is = matches.team_b
) as team_b_name
from matches "


This works on my local server but not working on my online server. Anyone know why? Is this bad technique?

Local server : MySQL client version: 3.23.49

Online server: MySQL client version: 4.1.10

Error: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select team_name from teams where team_is = matches.team_a
) a

Adam

It looks like a fairly convoluted method alright... Have a read here: http://www.unixreview.com/documents/s=8989/ur0407e/

The sub queries don't need to be where you have put them, sub queries in parentheses will be executed first regardless.

DublinWriter

Peter B wrote:

Is this bad technique?

Your design is ok, but you shouldn't use subqueries to get the team names, instead use two (inner) joins:

select matches.*, team_a.name, team_b.name
from matches, teams team_a, teams team_b
where matches.team_a = team_a.team_id and
matches.team_b = team_b.team_id

seamus

Are you sure you don't have those MySQL versions mixed up? IIRC subquery support was only brought into MySQL in v4. And it would seem more likely to me that your Web host would still be using 3.23 (Web hosts can tend to hold onto technologies a little longer for stability and compatibility issues).

What kind of output are you trying to acheive? There's usually more than one way to [strike]skin a cat[/strike] write a MySQL query.

Evil Phil

select *, 
(
      select team_name from teams where [U]team_is[/U] = matches.team_a
) as team_a_name,
(
      select team_name from teams where [U]team_is[/U] = matches.team_b
) as team_b_name
from matches 

[B]Error[/B]: #1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select team_name from teams where team_is = matches.team_a
) a

I might just be a typo in your code but it seems to have shown up in your error message as well. team_is (underlined above) should be team_id should it not?

I'd also do something similar to this for my query:

SELECT m.*, a.name, b.name
FROM matches m INNER JOIN
teams a ON m.team_id = a.team_id
INNER JOIN teams b ON m.team_id = b.team_id

jmcc

Peter B wrote:

This works on my local server but not working on my online server. Anyone know why? Is this bad technique?

Local server : MySQL client version: 3.23.49

Online server: MySQL client version: 4.1.10

MySQL versions prior to 4.* did not have sub selects. You have to rewrite the query in terms of JOINs for versions prior to 4.* as evil phil has done.

Regards...jmcc

Peter B

Sorry about the typo in the message. Yes I have managed to get that little problem sorted. What I posted was unfortunately a highly simplified version of my statment.

Unfortunately I run lots of my code on sub-querys.

Here is an example of an error I'm facing. This does work on my local server - 5.0.21-community-nt (Only worked this out when I used the 'SELECT VERSION()' query.).

My online server is '4.0.27-standard-log';

What I am trying to do here is get lots of info about a user, however the info must be calculated when the page is loaded. I didn't want to write lots of seperate SQL queries.

select
	
	u.username, 
	
	u.date_joined,
	
	(
	
		SELECT sum( points_gained )
		FROM predictions, match_results
		WHERE predictions.user_id = u.user_id and match_results.match_id = predictions.match_id
	) AS curr_points, 
	
	(
		SELECT count( * )
		FROM users
		WHERE 
		(
			SELECT sum( points_gained )
			FROM predictions, match_results
			WHERE predictions.user_id = users.user_id and match_results.match_id = predictions.match_id
		) = curr_points
	) AS users_with_same_points, 
	
	(
		SELECT min( pos )
		FROM (
		
			SELECT (
			@rownum := @rownum +1
			) AS pos, t . *
			FROM (
			
			SELECT @rownum :=0
			)r, (
			
			SELECT users.user_id, sum( points_gained ) AS points
			FROM users, predictions, match_results
			WHERE users.user_id = predictions.user_id and match_results.match_id = predictions.match_id
			GROUP BY users.user_id
			ORDER BY points DESC
			)t
		)l where points = curr_points
	) AS min_position,
	
	(
		SELECT count( * )
		FROM predictions, match_results
		WHERE predictions.user_id = u.user_id and match_results.match_id = predictions.match_id
	) AS num_pred_number,
	
	(
		SELECT sum( points_diff )
		FROM predictions, match_results
		WHERE predictions.user_id = u.user_id and 
		match_results.match_id = predictions.match_id
	) AS sum_points_diff,
	
	(
		SELECT count( * )
		FROM predictions, match_results, matches
		WHERE predictions.user_id = u.user_id and 
		match_results.match_id = predictions.match_id and 
		matches.match_id = match_results.match_id and
		predictions.correct_winner = 10
	) as correct_winner_predicted,
	
	(
		SELECT count(*) from predictions, matches, favourites, match_results
		WHERE 
		predictions.user_id = u.user_id and
		matches.match_id = predictions.match_id and
		favourites.competition_id = matches.competition_id and
		favourites.user_id = u.user_id and
		matches.match_id = match_results.match_id and
		(
			matches.team_a = favourites.team_id 
			or
			matches.team_b = favourites.team_id 
		)
	) as matches_containing_favourite,
	
	(
		SELECT count(*) from predictions, matches, favourites, match_results
		WHERE 
		predictions.user_id = u.user_id and
		matches.match_id = predictions.match_id and
		favourites.competition_id = matches.competition_id and
		favourites.user_id = u.user_id and
		matches.match_id = match_results.match_id and
		(
			matches.team_a = favourites.team_id 
			or
			matches.team_b = favourites.team_id 
		)
		and 
		(
			(
				match_results.team_a_score > match_results.team_b_score 
				and
				predictions.team_a_score > predictions.team_b_score 
			)
			or
			(
				match_results.team_a_score = match_results.team_b_score 
				and
				predictions.team_a_score = predictions.team_b_score 
			)
			or
			(
				match_results.team_a_score < match_results.team_b_score 
				and
				predictions.team_a_score < predictions.team_b_score 
			)
		)
	) as loyal_matches
		
	from users u
	where 
		user_id = "12"

jmcc

Peter B wrote:

Here is an example of an error I'm facing. This does work on my local server - definately version 3.23.49 - says it on phpmyadmin home page (where you select database from dropdown) under phpmyadmin version.

I just checked. That version does support a limited subselect.

What I am trying to do here is get lots of info about a user, however the info must be calculated when the page is loaded. I didn't want to write lots of seperate SQL queries.

Surprisingly, this is one of the things that people overlook with SQL - it can often be more efficient to write a series of small queries and to take the results from these queries to build fast summary tables. This problem seems like a classic application for this kind of solution. The match and stats data would not be continually (as in on a minute by minute basis) changing. So it is more likely that a summary type table that is generated or updated on a regular basis is a better solution than using a complex query to pull all the data from the tables each time the page is loaded because you will be only using a single table for the query.

Regards...jmcc

Native Tongue

Make sure you use the right cases aswell. I uploaded a page to my remote server the other day only to find out that the query wasn't working, despite it working fine on my local testing server. It ended up that I had the wrong case for the table name (equipment instead of Equipment).

democrates

It might be worth having a look at http://dev.mysql.com/doc/refman/4.1/en/optimization.html if you run into performance issues. Use Explain before a statement to see its execution plan, quite revealing.

Summary tables work great as jmcc said, keeping the sql simple also means the app can more easily migrate to another db.

I usually tend to take small result sets (bulk of data reduced by summary queries) into array variables (in php these days), free the expensive db connection, and traverse arrays to complete the summary, eg produce statistics using a diversity of fuss-pot rules. I'll fess up and admit array traversal has become somewhat of a fetish, it's not always technically the best solution but sometimes I can't stop MySelf.

Peter B

Sorry to bring this thread up again but how do you check your mysql version?

On my local server

"select version()" query => 5.0.21-community-nt

phpinfo() => Client API version 3.23.49


On my remote server
"select version()" query => 4.0.27-standard-log

phpinfo() => Client API version 4.0.27

Have I installed mySQL incorrectly? I may have messed around with different versions on my local server but which version do I believe?

I'm guessing I trust the phpinfo().