Understanding multiple joins with And

Question

I've come across an old database and was looking at some of the tables and sprocs.

I noticed a couple of things and wondered if I see syntax as below what does it mean?

Select tbl.id, tbl2.Name

From tbl

Left join tbl.id on tbl2.id AND
tbl.keeperId on tbl2.keeperId

So in short if there are two joins with an And does it mean it had to find rows in the tables where BOTH ids match?

Finally is a composite key the same as a foreign key? So a composite key in the above could be keeperId?

Answer 1

Hi @Mr Edge ,

Welcome to Microsoft Q&A!

So in short if there are two joins with an And does it mean it had to find rows in the tables where BOTH ids match?

The answer is YES. According to the query you provided, the joins needs to be matched with both columns (id and keepid).

Finally is a composite key the same as a foreign key?

The answer is NO.

The composite key is composed of two or more attributes that collectively uniquely identify each record. For example, the combination of house number and street might qualify as a composite key, given that the market listings are local.

Foreign key is an attribute which is a Primary key in its parent table, but is included as an attribute in another host table. For example, in addition to the Employee table containing the personal details of the employees, we might have another table Department containing information related to the department of the employee.

Composite key is a Candidate key that consists of more than one attribute.

Foreign key is an attribute which is a Primary key in its parent table but is included as an attribute in the host table.

They are not the same thing.

But you could create a Composite Foreign Key like below:

CONSTRAINT FK_FKName FOREIGN KEY  
 (FKColumn1, FKColumn2)  
REFERENCES PrimaryKeyTable (PKColumn1, PKColumn2)  

So a composite key in the above could be keeperId?

Actually you could firstly check the table structure and all keys of both tables and find out whether keeperId is one of composite key.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

And does it mean it had to find rows in the tables where BOTH ids match?

Right, both ID's must match

Finally is a composite key the same as a foreign key?

In database world we have primary key and foreign keys, which references the PK.
A PK can build over 1-n columns = composed primary key.