Help with complex SQL statement

[roblasch]

I have a table with 6 fields. It holds a game schedule for a football league. The fields are GameID, Week, HomeTeam, HomePoints, VisitingTeam, And VisitorPoints. I am trying to create a query that shows each teams schedule. It is easy enough to do either home games, or away games by sorting on the Home or Visitor field. But I need to have both (and have them grouped together). I want to do it with one trip to the server. Is it possible?

 

[puitar]

Change your fields to

GameID, Week, Team, Points

and add a field 'HomeAway' which could distinguish the record as either 'home' or 'away'.

 

[CFtaxtron]

Try

select * from tablename where HomeTeam= 'TeamName' or VisitingTeam = 'TeamName'

Tim
develop@embanet.com

 

[roblasch]

Let me explain further. Forgetting about points for the moment. The table holds the data for a football schedule. The SQL statement "SELECT HomeTeam, VisitingTeam FROM tblSchedule ORDER by Week" would return something like the following

Week 1 Bovine @ Edge
Week 1 Bombers @ Ryders
Week 1 Rats @ Jammers

Week 2 Ryders @ Bovine
Week 2 Jammers @ Edge
Week 2 Bombers @ Rats

What I want is to return the schedule sorted by team so that each teams schedule can be viewed like the following

Bovine
Week 1 @ Edge
Week 2 Ryders
Week 3 Edge
Week 4 @ Ryders

I want to get it all with one trip to the server. Can it be done?

 

[roblasch]

I figured it out thanks for the help