Left Join with Criteria?

[jchastai]

I have a standard setup of a table with a list of tools, a table with a list of users, and a table that contains the index of tools matched to the index of users (i.e. which user has access to which tool).

What I am needing to do is get a list of all of the tools to display, but also be able to know if the current user has access to the tool.

Put another way, I want to display all of the tools for the current user to see, but I am going to format the output differently depending on if he/she has access to the tool.

I am needing my output to be something like ...

Tool         Permission
-------      ------------------
Tool A       Admin Permission
Tool B       
Tool C       User Permission
etc.

My SQL so far is -

SELECT Tools.*, Tool_Permisssions.Permission
FROM Tools LEFT JOIN Tool_Permissions ON Tools.ID = Tool_Permissions.Tool
WHERE Tool_Permissions.User = 1 (the UserID I already have)

The problem is that this only gives me the tools that the user has access to, not all of them. I can see that the problem is in the where statement, but if I don't put that in, I get all the tools for all the users, not just the current one.

Anybody have a work around?

Thanks

 

[tlbroadbent]

This should work.

SELECT Tools.*, Tool_Permisssions.Permission
FROM Tools LEFT JOIN Tool_Permissions
ON Tools.ID = Tool_Permissions.Tool
WHERE Tool_Permissions.User = 1
OR Tool_Permissions.User Is Null Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.