Join

table 1 (tblClubs)
fields: clCode - clName
111 The Frogs
222 Easy riders
333 The butterflies


table 2 (tblCalendar)
fields: calDate - calHomeCode - calVisitorsCode

9/9/04 - 111 - 222
15/9/04 - 222 - 333

How to retrieve the name of the home playing club aswell as the name of the
visitor in Sqlstring ??

I have now:
sqlTxt = "SELECT * FROM tblClubs, tblCalendar WHERE clCode=calHomeCode OR
clCode=calVisitorsCode"

But when i want to display the Calendar :
Response.Write RS("calDate") & " - " & RS("clName") & " - " & RS("clName")

I have 2 times clName. I think i have to use a AS statement. But how?

Thx for the help

Maarten.

Re: Join

SELECT C.calDate,
A.Clname as HomeTeam,
B.Clname as VisitingTeam
FROM tblCalendar T
INNER JOIN tblClubs A
ON T.calHomeCode = A.ClCode
INNER JOIN tblClubs B
ON T.calHomeCode = B.ClCode

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com


"Maarten" wrote in message
news:od%gd.5186$di.307247@phobos.telenet-ops.be...
>
> table 1 (tblClubs)
> fields: clCode - clName
> 111 The Frogs
> 222 Easy riders
> 333 The butterflies
>
>
> table 2 (tblCalendar)
> fields: calDate - calHomeCode - calVisitorsCode
>
> 9/9/04 - 111 - 222
> 15/9/04 - 222 - 333
>
> How to retrieve the name of the home playing club aswell as the name of
> the visitor in Sqlstring ??
>
> I have now:
> sqlTxt = "SELECT * FROM tblClubs, tblCalendar WHERE clCode=calHomeCode OR
> clCode=calVisitorsCode"
>
> But when i want to display the Calendar :
> Response.Write RS("calDate") & " - " & RS("clName") & " - " & RS("clName")
>
> I have 2 times clName. I think i have to use a AS statement. But how?
>
> Thx for the help
>
> Maarten.
>
>
>
>
>
>
>
>
>
>
>

Re: Join

Hi Roji, Thx for the help. Little question: in the second inner join must ON
T.calHomeCode notbe T.calVisitorsCode ?


"Roji. P. Thomas" schreef in bericht
news:uSpH8$yvEHA.3916@TK2MSFTNGP10.phx.gbl...
> SELECT C.calDate,
> A.Clname as HomeTeam,
> B.Clname as VisitingTeam
> FROM tblCalendar T
> INNER JOIN tblClubs A
> ON T.calHomeCode = A.ClCode
> INNER JOIN tblClubs B
> ON T.calHomeCode = B.ClCode
>
> --
>>
>
>

Re: Join

Marteen,

it should be

SELECT C.calDate,
A.Clname as HomeTeam,
B.Clname as VisitingTeam
FROM tblCalendar T
INNER JOIN tblClubs A
ON T.calHomeCode = A.ClCode
INNER JOIN tblClubs B
ON T.calVisitorsCode= B.ClCode


--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com


"Maarten" wrote in message
news:aX4hd.5484$fl.330849@phobos.telenet-ops.be...
> Hi Roji, Thx for the help. Little question: in the second inner join must
> ON T.calHomeCode notbe T.calVisitorsCode ?
>
>
> "Roji. P. Thomas" schreef in bericht
> news:uSpH8$yvEHA.3916@TK2MSFTNGP10.phx.gbl...
>> SELECT C.calDate,
>> A.Clname as HomeTeam,
>> B.Clname as VisitingTeam
>> FROM tblCalendar T
>> INNER JOIN tblClubs A
>> ON T.calHomeCode = A.ClCode
>> INNER JOIN tblClubs B
>> ON T.calHomeCode = B.ClCode
>>
>> --
>>>
>>
>>
>
>