Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    45

    Unanswered: nested select query

    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

  2. #2
    Join Date
    Mar 2007
    Posts
    45
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by robasc View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •