Relationships Between Tables

1. Use SELECT to Specify Columns:

   • List the fields you want to retrieve from the tables.

2. Use FROM to Specify the Tables:

   • Specify the primary table and use a JOIN (table1.common_field) to connect the related table.

3. Use WHERE to Define the Relationship:

   • Compare columns from the tables using operators like =.
   • You can combine multiple conditions with logical operators (AND, OR).

Syntax

SELECT table1.column_name, table2.column_name
FROM table1, table2
WHERE table1.common_field = table2.common_field;

Example 1: Booking and Game

Retrieve all booking names where the booking name matches the game name:

SELECT Booking.Name, Game.Name
FROM Booking, Game
WHERE Booking.Name = Game.Name;

Example 2: Student and Loan

Retrieve all student names and loan details where the StudentID in the Student table matches the StudentID in the Loan table:

SELECT Student.Name, Loan.Amount
FROM Student, Loan
WHERE Student.StudentID = Loan.StudentID;

Rules for Logical Operators in Relationships

1. =: Use to check equality between fields in two tables.
   • Example: Booking.Name = Game.Name
2. Combine Conditions:
   • Use AND when both conditions must be true:

     WHERE Booking.Name = Game.Name AND Game.Price > 50;
     

   • Use OR when either condition can be true:

     WHERE Booking.Name = Game.Name OR Game.Price > 50;