Show one table that is linked by value to another

I am new to SQL and some points are difficult for me to understand. I have 2 tables:

Departments

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(45) NOT NULL,
head_of_department INT NOT NULL

Lectors

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
degree VARCHAR(45) NOT NULL,
salary INT NOT NULL

Departments.head_of_department refers to Lectors.id

I need to display lector who is head of department in Department with name X.

If it’s possible to select ALL table Lectors and ONLY table Lectors without listing all of its columns. For example, a table may have 100 columns and it would be inconvenient to write the names of all of them

Answer

SELECT Lectors.*
FROM Departments
INNER JOIN Lectors ON (Departments.head_of_department = Lectors.id)
WHERE Departments.name = 'Department Name You Want';

Advertisement

Answer