Newbie SQL Join Question

[DaytonGreg]

I have attempted to write a query with Joins to join Several Fields in One table to a general table. I am trying to produce a query which pulls the AttorneyShortName from the second table in place of the Multiple Attorney Fields in the Cases Table. I can perfom this action for one Join, but I cannot figure out how to do this with multiple fields.

Thanks for any help.

My first table is Cases. Here is the basic structure
CaseID
CaseName
CaseNumber
Attorney1
Attorney2
DefAttorney1
DefAttorney2
PlaAttorney1
PlaAttorney2

My second table is Attorney

AttorneyID
AttorneyShortName
AttorneyFirstName
AttorneyMiddleName
AttorneyLastName

 

[SBendBuckeye]

Hello DaytonGreg,

Here is an example of what I think you are trying to do with your tables. This uses a technique called a correlated subquery where we use something from an outer query to limit the inner query.

By enclosing the inner queries in parenthesis SQL processes them as a unit and will return only one row because we limited it to only one possibility by referencing the Cases AttorneyID. I included the AttorneyID in the subqueries to make testing easier. When you are done with it, just delete the Attorney.AttorneyID & " " & part.

Let's look at just the first one using Attorney1

Select C.Attorney1ID,
(SELECT A.AttorneyID & " " & A.AttorneyShortName
FROM Attorney A
WHERE A.AttorneyID = C.Attorney1ID) As ShName1
From Cases C

A and C are called Aliases and are used to make it easier to see things since you don't have to type out Attorney. or Cases. every time. You can use whatever you want to here.

This will return something like this:

Attorney1ID ShName1
4 4 ShortName

If you have any other questions or need further explanation please feel free to post again. Good Luck!

Complete example based on your table definitions above:

SELECT C.CaseID,
C.CaseName,
C.CaseNumber,
C.Attorney1ID,
(SELECT A.AttorneyID & " " & A.AttorneyShortName
FROM Attorney A
WHERE A.AttorneyID = C.Attorney1ID) As ShName1,
C.Attorney2ID,
(SELECT A.AttorneyID & " " & A.AttorneyShortName
FROM Attorney A
WHERE A.AttorneyID = C.Attorney2ID) As ShName2,
C.DefAttorney1ID,
(SELECT A.AttorneyID & " " & A.AttorneyShortName
FROM Attorney A
WHERE A.AttorneyID = C.DefAttorney1ID) As DefShName1,
C.DefAttorney2ID,
(SELECT A.AttorneyID & " " & A.AttorneyShortName
FROM Attorney A
WHERE A.AttorneyID = C.DefAttorney2ID) As DefShName2,
C.PlaAttorney1ID,
(SELECT A.AttorneyID & " " & A.AttorneyShortName
FROM Attorney A
WHERE A.AttorneyID = C.PlaAttorney1ID) As PlaShName1,
C.PlaAttorney2ID,
(SELECT A.AttorneyID & " " & A.AttorneyShortName
FROM Attorney A
WHERE A.AttorneyID = C.PlaAttorney2ID) As PlaShName2
FROM Cases C
ORDER BY C.CaseName;


Have a great day!

j2consulting@yahoo.com