The Explanation - (Joins)

[omarswan]

Hi,
Could someone explain to me purpose and the use of joins. An example wold be gladly appleciated.

Thanks

[browner]

Hi - if you have one table - Patients and another table - Doctors (or Employees and Managers, Teachers and Students etc) - joins are handy for enforcing data integrity.

Example

If you create the above two tables in Access - where Doctors has a DoctorID which is unique (autoNumber), and Patients has a DoctorID which is not unique (Number).

Then go into View -> Relationships, add the two tables, and drag DoctorID (doctor table) to DoctorID (patient table).

Access will ask you if you want to enforce referential integrity.

If you click yes it means that you cannot have a Doctor assigned to a patient unless thay are in the Doctors table. It also means that to find out a patients doctors name and address etc - you just look in the Doctors table intead of storing the information with the patient (takes less room). This also has the advantage that if a Doctor changes address etc - you only have to update one entry in one table instead of loads in the Patient table.

This by the way is the whole point of Relational Databases (i.e. nearly all databases in use today) I hope that is what you wanted - if not just ask. It is worth getting a book on if you plan designing a lot of databases.

[SteveFlitIII]

Another way of looking at it is using the one to many relationships. EG A human being has one mother however one mother can be the mother to many human beings