Difficult select statement

[chantey]

I have the set of tables:

MECHANIC (mechanic_id, name, competence_level)
CAR (car_ref_no, make, model, owner_name, value_category, car_reg_no)
BOOKING (car_ref_no, mechanic_id, job_name, date)

And I need to list the car_reg_no and make of any car that was worked on by all the mechanics.

For some reason subqueries don't work...(maybe there not meant to)

Any suggestions?

 

[hos2]

what version of mysql do you use ?? the older ones do'nt support subqueries.

 

[chantey]

It's version version: 3.23.58-nt

If it doesn'y support subqueries, is there another way of doing it without using them?

 

[vanekl]

I'm not exactly sure what you want but here are two ideas:

SELECT car_ref_no, make
FROM mechanic m, car c, booking b
WHERE c.car_ref_no = b.car_ref_no
AND b.mechanic_id = m.mechanic_id

SELECT car_ref_no, make
FROM car c, booking b
WHERE c.car_ref_no = b.car_ref_no
AND b.mechanic_id IN ( <mechID1>, <mechID2>, <mechID3>, ... )


[replace <mechIDs> with the IDs in the mechanic table]

 

[chantey]

To be more specific, here is the table BOOKING

+------------+-------------+----------------+------------+
| car_ref_no | mechanic_id | job_name | date |
+------------+-------------+----------------+------------+
| CR3 | 1 | Brake Pad | 2000-02-28 |
| CR6 | 1 | Gear Box | 1996-01-01 |
| CR10 | 1 | Lights | 2001-09-08 |
| CR14 | 1 | Battery | 2003-10-11 |
| CR11 | 1 | Brake Pad | 2000-06-02 |
| CR3 | 1 | Side Mirrors | 2001-09-11 |
| CR7 | 1 | Brake Pad | 1997-08-31 |
| CR2 | 1 | Bumper Replace | 2001-07-08 |
| CR6 | 1 | Battery | 2003-09-05 |
| CR8 | 2 | Re-spray | 2002-11-21 |
| CR15 | 2 | Bumper Replace | 1991-11-11 |
| CR15 | 2 | Lights | 1991-11-11 |
| CR10 | 2 | Side Mirrors | 1998-03-09 |
| CR11 | 2 | Gear Box | 1998-04-30 |
| CR5 | 2 | Tyres | 1996-10-27 |
| CR7 | 2 | Bumper Replace | 2000-09-24 |
| CR1 | 2 | Lights | 1999-06-16 |
| CR12 | 2 | Battery | 2001-08-21 |
| CR8 | 2 | Brake Pad | 2000-08-04 |
| CR8 | 3 | Doors | 2002-05-16 |
| CR1 | 3 | Tyres | 2003-01-31 |
| CR5 | 3 | Bumper Replace | 2003-11-19 |
| CR9 | 3 | Doors | 1999-01-03 |
| CR11 | 3 | Re-spary | 2000-12-28 |
| CR4 | 3 | Windscreen | 1996-10-20 |
| CR2 | 3 | Battery | 2002-12-10 |
+------------+-------------+----------------+------------+

There are mechanics 1, 2 and 3. Car CR11 has been worked on by mechanic 1, 2 and 3, hence all mechanics.
So how would I list the cars that have been worked on by all mechanics?

 

[vanekl]

here's one way,

SELECT c.car_ref_no, make
FROM car c, booking b1, booking b2, booking b3
WHERE c.car_ref_no = b1.car_ref_no
AND c.car_ref_no = b2.car_ref_no
AND c.car_ref_no = b3.car_ref_no
AND b1.mechanic_id = 1
AND b2.mechanic_id = 2
AND b3.mechanic_id = 3