Problem with a JOIN statement (I believe)

[Bene1]

I have been working on this for a while now, and I am getting very frustrated. Hopefully someone out there will be able to help...

I have two tables that I am linking together. I need to see all records out of the table 'GROUPS' and in another filed I need to see 'USER_NAME' from 'USER_ACCESS' if the link between the two tables exists, and if it does not, just a NULL. However, I want to be able to look at one USER_NAME at a time to see what 'GROUPS' they belong to, and everytime I try to use a WHERE clause I can no longer see all records from 'GROUPS'. Sorry if this is confusing, but any help would be greatly appreciated!!!!

Tried using LEFT OUTER JOIN (which I believe should work) and it did not, also have been trying to do a subquery to grab the records from USER_ACCESS with the correct USER_NAME first, but I can't seem to get that to work either...

 

[foxdev]

Can you post your statement? It seems like it should work based on your description.

[tt]select groups.*, useraccess.user_name
from groups
left outer join useraccess
on useraccess.key = groups.key [/tt]
Robert Bradley

http://www.agrainofhope.org

http://www.lowcostprints.com

http://www.foxdev.com

 

[Bene1]

Here is the code

SELECT groups.*, user_access.user_name
FROM groups LEFT OUTER JOIN
user_access ON user_access.group_name = groups.name

Except that this does not restrict by user_name. When I try to restrict it by user_name, then I lose the ability to view all of the records held in GROUPS....

 

[tlbroadbent]

Try this.

SELECT a.*, b.user_name
FROM groups As a
LEFT OUTER JOIN user_access As b
ON a.name = b.group_name
WHERE b.User_Name='name'
OR b.User_Name Is Null

Or this.

SELECT a.*, b.user_name
FROM groups As a
LEFT OUTER JOIN
(SELECT User_Name, Group_Name
FROM user_access
WHERE User_Name = 'name') As b
ON a.name=b.group_name Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Bene1]

Yes... the second suggestion worked perfectly. Thank you very much. I did not know that you could JOIN to a sub-query like that. Most useful...