asked on

complicated mySQL statement

I am creating a login, so I have a login form with three fields, companyId, username, password. customers and companies can both have one to many. which is why I have the affiliation table becasue he can have access to more then one company and he could have been granted or revoked permissions to access information from different companies.
I need to make sure that the query will not just login to any company cause he knows the ID and his username and password for his user profile are correct the record in the affiliation connecting the user and the company together and he must be granted access.

Thanks

my tables are as follows:

companies
companyId
allowAccessId

users
userId
username
password

companyUsersAffiliation
id
companyId
userId
allowAccessId

sysAccess
id
description (either active or inactive)
allowAccess (either true or false)

Lee Wadwell

What is your question?

prowebinteractiveinc

ASKER

looking for help on building the sql statement

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lee Wadwell

I forgot to mention for my alternative suggestion, that revokeAcessDate is populated with a silly future data (i.e. 2999-12-31) until set to an earlier date OR if left as NULL to indicate active, the SQL would need to be
and curdate() BETWEEN grantAccessDate and ISNULL(revokeAcessDate,curdate())

prowebinteractiveinc

ASKER

the only thing is I I user a company login username not Id which is in the companies table so line 7 is not good

Lee Wadwell

I thought you might but your description of the companies table did not include an name. So I guess it would be something like:

SELECT 1
FROM users 
INNER JOIN companyUsersAffiliation cua ON users.userId = cua.userId
INNER JOIN companies ON cua.companyId = companies.companyId
INNER JOIN sysAccess ON cua.allowAccessId = sysAccess.id AND sysAccess.allowAccess = 1
WHERE users.username = @username
AND users.password = @passwd
AND companies.companyName = @companyname

Open in new window

Be warned however ... if the company name is being typed in ... matching on name may need to include doing an UPPER() on each value to remove case differences; spelling mistakes would be problematic. If they were selecting from a drop-down (or ilk) from a list built from the companies table ... you should have kept the companyid with it in the background and used it on validation.