Noobie SQL Problem

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

July 29th, 2003, 01:04 PM

Ronage

Junior Member

Join Date: Jul 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Noobie SQL Problem

Situation:

Access Database

Goal: Construct a SQL statement to join data from two tables.

Table1 is a schedule

Fields are like this:

Date | Time | Team1 | Team2 |Location etc.

Table2 has the Team Names:

Team_ID | TeamName

I am trying to write a SQL statement that will give me the complete schedule for all teams which includes the Team Name and team number for each of team1 and team2.

this is not right, but it's a start :::>

SELECT S.GameDate, S.GameTime, S.LocationNumber, S.VisitingTeamNumber, S.HomeTeamNumber, T.TeamNumber, T.TeamName
FROM SchGames S, Team T
WHERE S.VisitingTeamNumber = T.TeamNumber;

TIA

July 29th, 2003, 05:06 PM

rkern

Contributing User

Join Date: Jul 2003

Posts: 33

Time spent in forums: < 1 sec
Reputation Power: 6

for starters, from what you have posted it looks like you will get results where the team numbers are equal. Not a likely situation, and you are limited to only that set.

I would define a common field (key) for each table, use 'games' or your 'gamedate'. Each team is identified with a game and select on that .

Just don't see any reason for selecting on team number.

cheers
RK

August 1st, 2003, 05:28 AM

Silian

Gogga

Join Date: Jul 2003

Posts: 198

Time spent in forums: < 1 sec
Reputation Power: 6

Not sure if this is what you are looking 4 (I'm assuming that team1 and team2 are the id's 4 the teams):

Code:

select a.date, a.time, a.location, b.teamName, c.teamName 
from table1 a
left join table2 b on (b.team_id = a.team1)
left join table2 c on (c.team_id = a.team2)

August 1st, 2003, 06:57 PM

Ronage

Junior Member

Join Date: Jul 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Close I think but getting Error

Thanks a lot for responding, I think it's definitely the right direction below although I am getting a syntax error.

I have attached a sample db that I tried the below on along with serveral variations to try and remedy the error with no luck.

Code:

SELECT S.GameDate, S.GameTime, H.TeamName, V.TeamName
FROM SchGames S
LEFT JOIN Team H ON (H.TeamNumber = S.HomeTeamNumber)
LEFT JOIN Team V ON (V.TeamNumber = S.VisitingTeamNumber);

Quote:

Originally posted by Silian
Not sure if this is what you are looking 4 (I'm assuming that team1 and team2 are the id's 4 the teams):

Code:

select a.date, a.time, a.location, b.teamName, c.teamName from table1 a left join table2 b on (b.team_id = a.team1) left join table2 c on (c.team_id = a.team2)

August 1st, 2003, 07:00 PM

Ronage

Junior Member

Join Date: Jul 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

File

Here it is in a Zip

Attached Files

test.zip (37.7 KB, 229 views)

August 4th, 2003, 02:32 AM

Silian

Gogga

Join Date: Jul 2003

Posts: 198

Time spent in forums: < 1 sec
Reputation Power: 6

I can't open your file (it tells me it does't recognise the format.) (Don't know if there is something wrong with my ms access) Can you give me the fields and types for the tables? (Or test the file, and if it isn't working, repost it)

Thanks,
Silian.

August 4th, 2003, 01:00 PM

Ronage

Junior Member

Join Date: Jul 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

New File and Screenshots....

Rename the attached file to test.mdb (from test.zip). Data types are number and text; Here are two screenshots of the tables with sample data...

Thanks a lot for your help!

Quote:

Originally posted by Silian
I can't open your file (it tells me it does't recognise the format.) (Don't know if there is something wrong with my ms access) Can you give me the fields and types for the tables? (Or test the file, and if it isn't working, repost it)

Thanks,
Silian.

Attached Files

test.mdb.zip (448.0 KB, 202 views)

August 4th, 2003, 02:32 PM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 26 m 4 sec
Reputation Power: 891

you didn't say what the problem was

try this:

Code:

select S.ScheduleNumber
     , S.GameNumber
     , S.GameDate
     , S.GameTime
     , H.TeamName as HomeTeam
     , V.TeamName as VisitingTeam
  from SchGames S
left outer
  join Team H 
    on S.HomeTeamNumber = H.TeamNumber
left outer
  join Team V 
    on S.VisitingTeamNumber = V.TeamNumber

rudy
http://rudy.ca/

August 4th, 2003, 02:45 PM

Ronage

Junior Member

Join Date: Jul 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Right, sorry about that. I keep getting a syntax error with the example above and below that reads:

Syntax error (missing operator) in query expression ‘S.HomeTeamNumber = H.TeamNumber
Left outer
Join Team V
On S.VisitingTeamnumber = V.TeamNumber’

Quote:

Originally posted by r937
you didn't say what the problem was

try this:

Code:

select S.ScheduleNumber , S.GameNumber , S.GameDate , S.GameTime , H.TeamName as HomeTeam , V.TeamName as VisitingTeam from SchGames S left outer join Team H on S.HomeTeamNumber = H.TeamNumber left outer join Team V on S.VisitingTeamNumber = V.TeamNumber

rudy
http://rudy.ca/

#10

August 4th, 2003, 02:52 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 26 m 4 sec
Reputation Power: 891

aw crap, my bad

that there was standard syntax

access requires parenthesized joins:

Code:

select S.ScheduleNumber
     , S.GameNumber
     , S.GameDate
     , S.GameTime
     , H.TeamName as HomeTeam
     , V.TeamName as VisitingTeam
  from (
       SchGames S
left outer
  join Team H 
    on S.HomeTeamNumber = H.TeamNumber
       ) 
left outer
  join Team V 
    on S.VisitingTeamNumber = V.TeamNumber

rudy

#11

August 4th, 2003, 03:03 PM

Ronage

Junior Member

Join Date: Jul 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Absolutely fabulous Rudy, thanks for the help, that is what I was looking for. I tried a couple () combos but could not find the right placement.

Regards,

Ron

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: