Jump to content

subquery in and IF() statement??


bcamp1973

Recommended Posts

ok, evidently this doesn't work...

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

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???
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

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

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

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

hope that makes more sense?  :-[
Link to comment
Share on other sites

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

[code]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;[/code]


me!
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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"
Link to comment
Share on other sites

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.

[code]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;[/code]


me!

Link to comment
Share on other sites

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

query...

[code]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;[/code]

data...

[code]--
-- 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);[/code]

me!
Link to comment
Share on other sites

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

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

hope that makes more sense?  i take it you teach CS courses?  that explains your patience :)
Link to comment
Share on other sites

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

[code]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;[/code]

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

[code]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;[/code]


me!
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.