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
you're still using the deprecated "implicit comma join" technique -- learn to use explicit JOIN syntax
, users.id as user
ON locations.location_pk = inspection_routine.location_fk
ON users.id = locations.user_fk
ON <font color='"#FF0000"'>staff.??? = users.??? </font>
WHERE inspection_routine.inspection_routine_pk = 24
are you sure you need the staff table at all?
This topic is now closed. New replies are no longer allowed.