Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query

Posted on 2004-10-31
5
Medium Priority
?
197 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
Comment
Question by:mvwmail
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:smaglio81
ID: 12458166
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
ID: 12458221
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
ID: 12458914
I don't know then. I don't have enough experience with Access and Coldfusion.

Steven
0
 
LVL 7

Accepted Solution

by:
INSDivision6 earned 400 total points
ID: 12459705
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
ID: 12460210
Fantastic..
works a treat.. thanks =)
0

Featured Post

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

610 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