Jump to content

Archived

This topic is now archived and is closed to further replies.

infekt_x

simple fix with JUST ONE query??

Recommended Posts

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

 

here are my tables:

table_ex.jpg\" border=\"0\" alt=\"IPB Im

 

thanks alot!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

 

:)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

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??

Share this post


Link to post
Share on other sites

[!--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--]

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.