simple fix with JUST ONE query??

[infekt_x]

Im having a problem getting the results im looking for using only ONE query...

 

here are my tables:

 

thanks alot!

[fenway]

This would be very ugly without subselects. Try the following in 4.1+:

 

SELECT ( SELECT team_name FROM tbl_teams WHERE uid = team_home ) AS homeTeam, ( SELECT team_name FROM tbl_teams WHERE uid = team_away ) AS awayTeam FROM tbl_games;

 

Feel free to add a CONCAT() to get the formatted output; personally, I'd do it in PHP.

 

Hope that helps.

[infekt_x]

uid? is that team_id?

 

also, team_home (in the first sub select) is in the games table, yet the sub select is only addressing the teams table.... do I need to do a JOIN?

 

thanks for you help!

 

[fenway]

Yeah, it's team_id -- I always use uid for my own table, sorry, force of habit. And you can implicitly refer to columns in the outer query without joining -- that's the beauty of it. To make it more clear, you can always alias the outer table:

 

SELECT ( SELECT team_name FROM tbl_teams WHERE team_id = g.team_home ) AS homeTeam, ( SELECT team_name FROM tbl_teams WHERE team_id = g.team_away ) AS awayTeam FROM tbl_games AS g;

 

Hope that helps.

[infekt_x]

that makes sooo much sense..

but I keep getting an error.

 

here is my sql:

 

$sql=mysql_query("SELECT (SELECT team_name FROM tbl_teams WHERE team_id='g.team_home') AS home_team, (SELECT team_name FROM tbl_teams WHERE team_id='g.team_away') AS away_team FROM tbl_photo_games AS g"); 

 

i have tried the above code without the single quotes around the team_id='g.team_home' as well.

 

and the error:

 

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

 

does it need to be handled differently as its in php?

[fenway]

First, is there an error produce by mysql_error()? The invalid resource suggests that the query isn't being executed properly. mysql_error() will tell you why -- try to figure it out, or post it here. LazyJones suggests the following:

 

$sql = "SELECT ....";
$result = mysql_query($sql) or die ("ERROR: ".mysql_error()." with query: $sql");

 

Hope that helps.

[infekt_x]

 

Here is the error:

 

ERROR: 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 tbl_teams WHERE team_id=g.team_home with query:

 

the query isn't showing up cause $sql is empty.

[infekt_x]

looks like subqueries aren't supported until mysql 4.1 and im running 4.0 as fenway already mentioned I needed above.

 

 

thanks fenway!

 

any solutions for mysql 4.0, anyone??

[shoz]

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]

t1.team_name, t2.team_name

FROM tbl_games AS g

INNER JOIN tbl_teams AS t1

ON t1.team_id = g.team_home

INNER JOIN tbl_teams AS t2

ON t2.team_id = g.team_away

[!--sql2--][/div][!--sql3--]

[infekt_x]

shoz! that is it!

 

thanks alot man!

thanks both you guys. (fenway/shoz)

 

 

[fenway]

Indeed -- usually you can rewrite any subselect that doesn't use an aggregate function (e.g. MAX()) using a JOIN.