MySQL multi table join query

Question

Result needed: The registration number of the cars used by Instructors at the Glasgow, Bearsden office. The 3 relevant tables I have are; ( I have omitted the non-relevant table information, to simplify this. The database is called easydrive.

No this is not a School or UNI assignment, it is a question from a text book, that we have been given to do so we can learn how to do it, no marks are awarded for this, so you are not doing my homework for me.

**++Staff++**
(PK) Staff ID
(FK) Address ID
(FK) Office
(FK) Car Allocation Number
First Name
Last Name
Position/Title
Office

**++CarAllocation++**
(PK) Car Allocation Number
(FK) Staff ID
(FK) Car ID

**++Car++**
(PK) Car ID
Car Rego

So I need to so a join I think and I think it needs to go something along these lines but am very confused.

SELECT car.rego
FROM car
WHERE  staff.office=’Glasgow’ OR ‘Bearsden’

Can someone please fill in the blanks so I can learn how to perform this, do I need to make a new table?

Answer 1

As far as car_allocation is an intermediate table, it's rather better to put it into the FROM clause and JOIN others:

SELECT car.rego
FROM car_allocation A
JOIN car ON A.car_id = car.car_id
JOIN stuff ON A.stuff_id = stuff.stuff_id
AND (staff.office = 'Glasgow' OR staff.office = 'Bearsden' -- OR some another condition, etc)

Pay attention to brackets, they groups conditions.

or

SELECT car.rego
FROM car_allocation A
JOIN car ON A.car_id = car.car_id
JOIN stuff ON A.stuff_id = stuff.stuff_ids
AND staff.office IN ('Glasgow', 'Bearsden')

If you have an enumeration you can use IN operator.

Also JOIN means the same as INNER JOIN. Here's MySQL manual considering JOIN syntax

Answer 2

You query most probably needs to look something like this:

SELECT first_name, last_name, car_rego
FROM   staff
JOIN   carallocation ON (carallocation.staff_id = staff.staff_id)
JOIN   car ON (car.car_id = carallocation.car_id)
WHERE  staff.office = 'Glasgow' OR staff.office = 'Bearsden';

Note that JOIN is a synonym for an INNER JOIN in MySQL.

Answer 3

I haven't tested it but this should help.

select car.rego
from car
inner join carrallocation ca on ca.carid = car.carid
inner join staff s on ca.staffid = s.staffid
where s.office in ('Glasgow', 'Bearsden')

What you are missing is the joins between the tables. You need to join to the intermediary table to get the relationship between the staff and car tables.