Help with Query - Access

Question

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 ?

Fionnuala · Accepted Answer · 2010-03-08 12:05:12Z

1

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

edited Mar 8, 2010 at 12:05

answered Mar 8, 2010 at 11:19

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Johannes Rudolph · Accepted Answer · 2010-03-08 10:32:53Z

0

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)

answered Mar 8, 2010 at 10:32

Johannes Rudolph

35.8k15 gold badges118 silver badges175 bronze badges

3 Comments

aco Over a year ago

Thank you. And the machines with not any user linked?

Fionnuala Over a year ago

I do not think that the join type will address the question. Especially not a Right Join.

Fionnuala Over a year ago

That's as maybe, but the OP already has the correct join type IMHO.

Patrick Honorez · Accepted Answer · 2010-03-08 11:30:00Z

0

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.

answered Mar 8, 2010 at 11:30

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Collectives™ on Stack Overflow

Help with Query - Access

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related