Query repeats for each user - a join problem?

Databases
1

Hi all

I have the following query

SELECT inspection_routine.inspection_routine_pk, users.id as user, locations.location_pk
                                                FROM inspection_routine, staff, users, locations
                                                WHERE locations.user_fk = users.id
                                                AND inspection_routine.inspection_routine_pk = 24

However, this repeats the records for each user

what I am trying to do is given an inspection_routine_pk, tell me the user_id and the location_pk

location_pk is held in the inspection_routine table (as location_fk) so that should be ok, but to get the user_id I have to use the location_pk, query the locations table to get the user_id

I am sure this is simple but I have been looking at it for too long now and my mind is baffled…

Thanks for any help

Matt

2

you’re still using the deprecated “implicit comma join” technique – learn to use explicit JOIN syntax

SELECT inspection_routine.inspection_routine_pk
     , users.id as user
     , locations.location_pk
  FROM inspection_routine
INNER
  JOIN locations
    ON locations.location_pk = inspection_routine.location_fk
INNER
  JOIN users
    ON users.id = locations.user_fk     
INNER
  JOIN staff
    ON [COLOR="#FF0000"]staff.??? = users.???    [/COLOR]
 WHERE inspection_routine.inspection_routine_pk = 24

are you sure you need the staff table at all?