Solved

SQL Query

Posted on 2004-10-31
145 Views
Last Modified: 2013-12-24
Hopefully a simple one, thouhg I am too knackered to work it out (lazy oik)

I have a MATCH table which has a home team and an away team

MatchID (PK)
HomeTeam
AwayTeam

Both the Home and Away team pull off the team from the same table of teams

TEAMS
TeamID (PK)
TeamName

There is more information but it is not important.

I know how to code a simple SELECT staement using a JOIN to pull of the name of ONE of the teams, but have never done it for TWO.. any help appreicated.

Thanks
0
Question by:mvwmail
    5 Comments
     
    LVL 5

    Expert Comment

    by:smaglio81
    SELECT      tone.TeamName as HomeTeam, ttwo.TeamName as AwayTeam
    FROM      m INNER JOIN
                teams tone ON m.HomeTeam = tone.TeamID INNER JOIN
                teams ttwo ON m.AwayTeam = ttwo.TeamID
    WHERE      m.MacthID = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#" />
    0
     
    LVL 3

    Author Comment

    by:mvwmail
    My app does not like that at ALL, throws a wobbler.. and I cant work throuhg it enouhg to debug. Dont know if it makes a difference but my database is Access.

    Will have another go in a bit.. need coffee
    0
     
    LVL 5

    Expert Comment

    by:smaglio81
    I don't know then. I don't have enough experience with Access and Coldfusion.

    Steven
    0
     
    LVL 7

    Accepted Solution

    by:
    Try this:

    SELECT c.MatchID,a.TeamName as HomeTeamName,b.TeamName as AwayTeamName
    FROM TEAMS a,TEAMS b,MATCH c
    WHERE c.MatchID=#MatchID# and c.HomeTeam=a.TeamID and c.AwayTeam=b.TeamID



    0
     
    LVL 3

    Author Comment

    by:mvwmail
    Fantastic..
    works a treat.. thanks =)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
    If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…
    In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…

    931 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now