Acquiring information from 3 seperate tables. 1

[Malaphus]

I have 3 tables. USERS, GROUPS, and GROUPMEMBERSHIP

The USERS table has 3 fields, ID, USER_NAME, and USER_PASS

The GROUPS table has 2 fields, ID, GROUP_NAME

The GROUPMEMBERSHIP table has 3 fields, ID, USER_ID, GROUP_ID

Now, I have 3 values: the username, the password, and a group name I want to see if they belong to.

What I'd like to know is, how I would go about sort of linking all 3 of these tables?

Like if I have a user with ID "5", USER_NAME "malaphus", USER_PASS "mypass", a group with ID "13", GROUP_NAME "admins", and a record in the groupmembership table linking user id 5 to group 13 via the fields ID 1, USERID 5, GROUPID 13.

I'd want to return a record if there is a user in the users table with user_name = malaphus, user_pass = mypass, and he belongs to group "admins". If the user_name, user_pass dont match any record, return 0 records, or if the user_id that does match doesnt belong to group_name admins, also return 0.

I know I can do this with multiple queries, but I was hoping for a nifty join of some sort that can do it in only 1 query.

Thanks in advance.

 

[PHV]

Something like this ?
SELECT USER_NAME, USER_PASS, GROUP_NAME
FROM (GROUPMEMBERSHIP M
INNER JOIN GROUPS G ON M.GROUP_ID = G.ID)
INNER JOIN USERS U ON M.USER_ID = U.ID
WHERE user_name = 'malaphus' AND user_pass = 'mypass' AND group_name = 'admins';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Malaphus]

That is exactly what I was looking for! Works like a charm. Thanks a ton!

-Mal