Joining on Multiple Fields

Suppose you have a database for the National Cross Country Golfing Association, with the following tables:

The ClubMemberID in this example is a number assigned by the individual club to which the member belongs, and is therefore not unique within the table. But the combination of ClubID and ClubMemberID *is* unique, and constitutes a composite primary key.

Suppose that there is a Receipt table which uses the ClubID and ClubMemberID as a composite foreign key:

To return information from the Membership table together with information from the Receipt table, you would use a query such as:

SELECT Membership.Surname, Receipt.ReceiptDate, Receipt.Amount
FROM Membership INNER JOIN Receipt
ON Receipt.ClubID=Membership.ClubID
AND Receipt.ClubMemberID=Membership.ClubMemberID

Or, using the "Old Style" syntax mentioned in my previous tip:

SELECT Membership.Surname, Receipt.ReceiptDate, Receipt.Amount
FROM Membership, Receipt
WHERE Receipt.ClubID=Membership.ClubID
AND Receipt.ClubMemberID=Membership.ClubMemberID

Now, if you are designing a database from scratch, there is a good reason for not following this particular design pattern, but it is worth knowing how to deal with composite keys when you do come across them.

One last thing. To return information from the Club table as well, use a nested join:

SELECT Club.ClubName, Membership.Surname, Receipt.ReceiptDate, Receipt.Amount
FROM (Club INNER JOIN Membership ON Membership.ClubID=Club.ClubID)
INNER JOIN Receipt
ON Receipt.ClubID=Membership.ClubID
AND Receipt.ClubMemberID=Membership.ClubMemberID