nested select query

robasc · #1 (**permalink**) 11-01-10, 17:23

I am trying to retrieve the name of the visitor team by using a nested select query but all I am returning is a 1 or 0 which is not what I want. I want the name.

Code:

SELECT t.team_name AS Home, t.team_name 
IN (select t.team_name from teams t, 
games g WHERE t.teams_id = g.visitor) AS Visitor
FROM games g, teams t
WHERE g.home = t.teams_id
ORDER BY g.game_date;

could anyone tell me what I am doing wrong or what I need to do to return the name?

Thanks

robasc · #2 (**permalink**) 11-01-10, 19:12

never mind I figured it out:

Code:

SELECT t1.team_name as Home, g.home_score,  t2.team_name as visitor, g.visitor_score, 
g.game_date, g.game_time, g.venue
FROM games g, teams t1, teams t2
where g.visitor = t1.teams_id
and g.home = t2.teams_id
order by g.game_date

r937 · #3 (**permalink**) 11-01-10, 22:50

Quote:

Originally Posted by robasc

never mind I figured it out:

congrats to you

however, you're joining on the wrong columns

you have t1.team_name as Home, but you're joining t1.teams_id to g.visitor!

also, you could improve on the query structure a little bit...

Code:

SELECT home.team_name as home
     , games.home_score
     , away.team_name as visitor
     , games.visitor_score
     , games.game_date
     , games.game_time
     , games.venue
  FROM games
INNER
  JOIN teams as home
    ON home.teams_id = games.home
INNER
  JOIN teams AS away
    ON away.teams_id = games.visitor
ORDER 
    BY games.game_date
     , games.game_time

Internet Services

Web Development

Digital Marketing

Consumer Tech