Joining 2 Foreign keys in a table

I have a table that is named CliCore and has the following fields,
RegNo - Primary Key
CliFName - First Name
CliMM - Middle Initial
CliLName - Last Name
CliDOB - Date of Birth
I have another table that is named CliEvents and has the following fields,
UID - Primary Key
RegNo - Foreign Key from CliCore table
AggressorRegNo - Same as above
EventCatID - Category ID
Comments - Event Comments
How can I get the names of the clients for both RegNo and AggressorRegNo?
Thanks,
Drewyou have to refer 2 times to the CliCore table...something like:
select ... from clievents inner join CliCore A on CliEvents .RegNo =
A.RegNo inner join CliCore B on CliEvents .AggressorRegNo = B.RegNo
Francesco Anti
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OYAKkK0RFHA.1176@.TK2MSFTNGP12.phx.gbl...
>I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>|||SELECT E.uid,
E.regno, C1.clilname,
E.aggressorregno, C2.clilname,
E.eventcatid, E.comments
FROM CliEvents AS E
JOIN CliCore AS C1
ON E.regno = C1.regno
JOIN CliCore AS C2
ON E.aggressorregno = C2.aggressorregno
David Portas
SQL Server MVP
--
(untested)|||Try,
select a.CliFName, a.CliLName, b.CliFName, b.CliLName
from CliEvents as e inner join CliCore as a on e.RegNo = a.RegNo
inner join CliCore as b on e.AggressorRegNo = b.RegNo
AMB
"Drew" wrote:

> I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>
>|||Thank you all for the replies... I was trying it like this and it wasn't
working...
SELECT...
FROM Events E INNER JOIN CliCore CC ON E.RegNo = CC.RegNo OR
E.AggressorRegNo = CC.RegNo...
Thanks a bunch for clearing this up!
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OYAKkK0RFHA.1176@.TK2MSFTNGP12.phx.gbl...
>I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>