Jump to content

simple fix with JUST ONE query??


infekt_x

Recommended Posts

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.

Link to comment
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.

Link to comment
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?

Link to comment
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.

Link to comment
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.

Link to comment
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--]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.