Jump to content


Photo

simple fix with JUST ONE query??


  • Please log in to reply
10 replies to this topic

#1 infekt_x

infekt_x
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 13 January 2006 - 07:01 PM

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

here are my tables:
[img src=\"http://www.hagadonenewmedia.com/table_ex.jpg\" border=\"0\" alt=\"IPB Image\" /]

thanks alot!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 January 2006 - 07:46 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 infekt_x

infekt_x
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 13 January 2006 - 07:55 PM

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!

:)

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 January 2006 - 08:08 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 infekt_x

infekt_x
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 13 January 2006 - 09:51 PM

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[/quote]

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

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 January 2006 - 10:05 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 infekt_x

infekt_x
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 13 January 2006 - 10:13 PM


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.

#8 infekt_x

infekt_x
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 13 January 2006 - 10:48 PM

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

#9 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 13 January 2006 - 11:23 PM

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

#10 infekt_x

infekt_x
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 13 January 2006 - 11:36 PM

shoz! that is it!

thanks alot man!
thanks both you guys. (fenway/shoz)



#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 January 2006 - 11:48 PM

Indeed -- usually you can rewrite any subselect that doesn't use an aggregate function (e.g. MAX()) using a JOIN.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users