Help with Query - Access

Question

Hi

I have 4 tables: machines, categories, users and usersMAchines

machines is linked to categories and usersMachines.

Machines
--------
idMachine 
machine
idCat

Categories
--------
idCat
category

Users 
--------   
idUser
nameUser

UsersMachines
--------
idUserMachine
idUser
IdMachine

To list machines, filtered for any field of 3 previous table, I have this query:

select distinct machines.*,categories.category  
from( (machines 
left join  UsersMachines 
on machines.idMachine=UsersMachines.idMachine) 
left join categories 
on machines.idcat=categories.idcat)

Ok,runs fine.

But Using the same query, how I can do to filter the machines that only have linked users ?

Answer 1

Use a right or inner join on the userMachine table.

  select distinct machines.*,categories.category  
    from( (machines right join  UsersMachines on machines.idMachine=UsersMachines.idMachine) left join categories on machines.idcat=categories.idcat)

Answer 2

As far as I can see, you need to add the iduser field (column) from UsersMachines and check if it is null.

SELECT DISTINCT machines.*, categories.category, UsersMachines.idUser
FROM (machines 
LEFT JOIN UsersMachines 
ON machines.idMachine = UsersMachines.idMachine) 
LEFT JOIN categories 
ON machines.idcat = categories.idcat
WHERE UsersMachines.idUser Is Not Null

Answer 3

Use and outer join, and filter for null value in the linked table.
By the way, there is a wizard that helps you to do this in all versions of Access. It is called "Find unmatched query wizard".
Alternatively, you can also use the IN construct:

SELECT * FROM MACHINES
WHERE machineId NOT IN 
(SELECT DISTINCT MachineId FROM USerMachine)

but this is generally slower to run in Access.