Jump to content


Photo

subquery in and IF() statement??


  • Please log in to reply
12 replies to this topic

#1 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 05 October 2006 - 12:58 AM

ok, evidently this doesn't work...

IF(home_id=12151,(SELECT long_name FROM teams WHERE id=visitor_id),(SELECT long_name FROM teams WHERE id=home_id)) AS opponent

ok, i have a team's ID. i'm querying a table that has the id of both the home and away teams to build a schedule for that team.  i want to return the name of the oppenent which will correspond the the id that i *don't* have...ugh.  it doesn't even make sense when i write it myself...

here are the two tables...

games
---------
id
date
time
visitor_id (teams.id)
home_id (teams.id)

teams
---------
id
long_name
etc.

any suggestions???


#2 mjlogan

mjlogan
  • Members
  • PipPipPip
  • Advanced Member
  • 122 posts

Posted 05 October 2006 - 04:09 PM

Let me get this right, you one table full of fixtures and one table full of teams.

You want to query the fixtures table for your teams ID in either the

visitor_id (teams.id)
home_id (teams.id)

columns and return both IDs. You also need a join in the query to get the team names from the 'teams' table.

#3 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 05 October 2006 - 04:27 PM

thanks for taking the time to try to help me with this.  i know i'm not making much sense, let me try again. the purpose of the query is to build a schedule for a specific team. when i make the query all i know is the team's id.  the structure of the tables can't be altered as it's actively in use.  i want to build an html table like the following from the query.

<table>
<tr><td>[date]</td><td>[location]</td><td>[opponent]</td><td>[time]</td></tr>
<tr><td>[date]</td><td>[location]</td><td>[opponent]</td><td>[time]</td></tr>
<tr><td>[date]</td><td>[location]</td><td>[opponent]</td><td>[time]</td></tr>
...
</table>

so each row in the html table corresponds to a game the team has to play this season.  I've been able to build my query to capture everything correctly, but one thing.  The name (long_name) of the opponent team. The if() statement above was my attempt at capturing that.  Assuming a team id of 12151 here's the whole query...

SELECT 
	DATE_FORMAT(date,'%b %D %Y') AS date,
	time,
	IF(home_id=12151,"Home","Away") AS location,
	IF(home_id=12151,(SELECT long_name FROM nhl_teams WHERE id=visitor_id),(SELECT long_name FROM nhl_teams WHERE id=home_id)) AS opponent
FROM
	nhl_games, nhl_teams
WHERE 
	visitor_id=12151 OR home_id=12151
ORDER BY
	date DESC

hope that makes more sense?  :-[

#4 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 05 October 2006 - 05:13 PM

If your doing a sub query then you don't need the JOIN, you could do this with a JOIN and a UNION, but it looks like you want a sub query, so here it is

Something like...

SELECT DATE_FORMAT(g.date,'%b %D %Y') AS date, g.time, (SELECT long_name FROM nhl_teams WHERE id = g.home_id ) AS home_team, 
(SELECT long_name FROM nhl_teams WHERE id = g.visitor_id ) AS away_team FROM nhl_games AS g WHERE home_id = 1 OR visitor_id = 1 ORDER BY date DESC;


me!

#5 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 05 October 2006 - 05:51 PM

it doesn't have to be a subquery really, anything as long as it works :)  this is close to what i'm looking for but not quite there.  instead of capturing the home team and away team, i need to capture the opponent as i want the resulting table to look like this...

DATE          LOCATION    OPPONENT      TIME
---------------------------------------------------------
Oct 3 2007  Away          New York        7:30 PM
Oct 5 2007  Home          Washington    7:30 PM

#6 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 05 October 2006 - 05:55 PM

P.S. For some reason your query is returning an error, i can't figure out why, the syntax looks right to me...it's typical check your sytnax 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 long_name FROM nhl_teams WHERE id=g.home_id) AS home_tea"

#7 fenway

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

Posted 05 October 2006 - 07:13 PM

Are you sure you have subquery support (4.1+)?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 05 October 2006 - 07:17 PM

oh he!!...i don't...that server is running 4.0.17...crap :(

#9 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 05 October 2006 - 09:50 PM

That why it a good idea, to always post your DB version and your database scheme, it helps other people help you. It's like I tell my students, how in the world can I answer the equation when I don't have all the data!

Anyway...

// notes!

replace every * 1 * in the query with the team id you want to list the schedule for.

SELECT DATE_FORMAT(ta.date,'%b %D %Y') AS date, ta.time, tb.long_name AS home_team, tc.long_name AS away_team, FROM nhl_games ta LEFT OUTER JOIN nhl_teams tb ON(ta.home_id = tb.id) LEFT OUTER JOIN nhl_teams tc ON(ta.visitor_id = tc.id) WHERE ta.home_id = 1 OR ta.visitor_id = 1 ORDER BY ta.date DESC;


me!



#10 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 05 October 2006 - 10:03 PM

heres my test data so you see how it works...

query...

SELECT ta.*, tb.tn AS home_team, tc.tn AS away_team FROM games ta 
LEFT OUTER JOIN teams tb ON(ta.home_id = tb.tid) 
LEFT OUTER JOIN teams tc ON(ta.visitor_id = tc.tid) 
WHERE ta.home_id = 1 OR ta.visitor_id = 1;

data...

-- 
-- Database: `baseball`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `games`
-- 

CREATE TABLE `games` (
  `gid` int(10) unsigned NOT NULL auto_increment,
  `gd` date NOT NULL,
  `home_id` int(10) NOT NULL,
  `visitor_id` int(10) NOT NULL,
  `ws` tinyint(2) NOT NULL default '0',
  `ls` tinyint(2) NOT NULL default '0',
  `wt` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`gid`),
  KEY `home_id` (`home_id`,`visitor_id`,`wt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `games`
-- 

INSERT INTO `games` VALUES (1, '2006-10-19', 1, 4, 0, 0, 0);
INSERT INTO `games` VALUES (2, '2006-10-19', 2, 3, 0, 0, 0);
INSERT INTO `games` VALUES (3, '2006-10-20', 4, 3, 0, 0, 0);
INSERT INTO `games` VALUES (4, '2006-10-20', 2, 1, 0, 0, 0);
INSERT INTO `games` VALUES (5, '2006-10-21', 1, 3, 0, 0, 0);
INSERT INTO `games` VALUES (6, '2006-10-21', 4, 2, 0, 0, 0);
INSERT INTO `games` VALUES (7, '2006-10-22', 4, 1, 0, 0, 0);
INSERT INTO `games` VALUES (8, '2006-10-22', 3, 2, 0, 0, 0);
INSERT INTO `games` VALUES (9, '2006-10-23', 3, 4, 0, 0, 0);
INSERT INTO `games` VALUES (10, '2006-10-23', 1, 2, 0, 0, 0);
INSERT INTO `games` VALUES (11, '2006-10-24', 3, 1, 0, 0, 0);
INSERT INTO `games` VALUES (12, '2006-10-24', 2, 4, 0, 0, 0);

-- --------------------------------------------------------

-- 
-- Table structure for table `teams`
-- 

CREATE TABLE `teams` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `tn` varchar(120) NOT NULL,
  `tw` tinyint(3) NOT NULL default '0',
  `tl` tinyint(3) NOT NULL,
  PRIMARY KEY  (`tid`),
  KEY `tn` (`tn`,`tw`,`tl`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `teams`
-- 

INSERT INTO `teams` VALUES (1, 'Killer Bees', 0, 0);
INSERT INTO `teams` VALUES (2, 'Black Widows', 0, 0);
INSERT INTO `teams` VALUES (3, 'Hound Dogs', 0, 0);
INSERT INTO `teams` VALUES (4, 'Rengade Indains', 0, 0);

me!

#11 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 06 October 2006 - 12:26 AM

thanks for all your attention printf!  unfortunately we're still not quite there yet.  i apologize if my description is confusing  :-[  I need the results to display as follows...

          **Schedule for New Jersey**
---------------------------------------------------------
DATE          LOCATION    OPPONENT      TIME
---------------------------------------------------------
Oct 3 2007  Away          New York        7:30 PM
Oct 5 2007  Home          Washington      7:30 PM
etc...
---------------------------------------------------------

so grabbing, both the home and away team name doesn't fit the result set i need. the following query will get me everything, but the "opponent" value.  that's the one i'm having such issues capturing properly.  the oppnent may be the away team OR the home team since the schedule itself is for a single team...

SELECT
	DATE_FORMAT(g.date,'%b %D %Y') AS date,
	g.time,
	IF(g.home_id=12151,"Home","Away") AS location,
	** GET OPPONENT SOMEHOW **
FROM
	nhl_games AS g
WHERE
	home_id=12151
OR
	visitor_id=12151
ORDER BY
	date DESC

hope that makes more sense?  i take it you teach CS courses?  that explains your patience :)

#12 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 06 October 2006 - 03:21 AM

You can still use what I gave just change the SELECT part of the query! I was only giving an example of returning both team names, the format was up to you!

So, same thing as before

replace every * 1 * in the query with the team id you want to list the schedule for.


1. first query does exactly like your example, I guess it's what you want...

SELECT DATE_FORMAT(ta.date,'%b %D %Y') AS date, ta.time, IF(ta.home_id=1,'Home','Away') AS location, IF(ta.home_id=1, tc.long_name, tb.long_name) AS opponent FROM nhl_games ta LEFT OUTER JOIN nhl_teams tb ON(ta.home_id = tb.id) LEFT OUTER JOIN nhl_teams tc ON(ta.visitor_id = tc.id) WHERE ta.home_id = 1 OR ta.visitor_id = 1 ORDER BY ta.date DESC;

2. this query does what you want, but also returns the schedule title team, used for the maybe the schedule title, maybe not what you want, but it's there, because I didn't see where you were getting the title from!

SELECT DATE_FORMAT(ta.date,'%b %D %Y') AS date, ta.time, IF(ta.home_id=1,'Home','Away') AS location, IF(ta.home_id=1, tc.long_name, tb.long_name) AS opponent, IF(ta.home_id=1, tb.long_name, tc.long_name) AS title FROM nhl_games ta LEFT OUTER JOIN nhl_teams tb ON(ta.home_id = tb.id) LEFT OUTER JOIN nhl_teams tc ON(ta.visitor_id = tc.id) WHERE ta.home_id = 1 OR ta.visitor_id = 1 ORDER BY ta.date DESC;


me!

#13 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 06 October 2006 - 04:58 PM

Awesome!  It's working, thanks for your time (and patience :) )!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users