Forum Moderators: open
Login table
Columns: coach_id (primary key), username, password
Employee table
Columns: emp_id (primary), coach_id, emp_name, info
My problem is that I can't get a query correct to cover the login and list the employees assigned to the coach. I'm getting back employees that aren't assigned to that coach. Here is the query I'm using:
SELECT login.coach_id, employee.emp_id, employee.coach_id, employee.emp_name, login.username, lgin.password
FROM [login], [employee]
WHERE login.coach_id = employee.coach_id
I'm not sure what I need to add or what I'm doing wrong. Any help would be great! Thanks!
I'm assuming you have already checked the login credentials at that point. Or you can combine them together:
SELECT login.coach_id, employee.emp_id, employee.coach_id, employee.emp_name, login.username, lgin.password
FROM [login], [employee]
WHERE login.coach_id = employee.coach_id AND login.username='#FORM.username#' and login.password='#FORM.password#'
[edited by: LifeinAsia at 4:31 pm (utc) on Feb. 26, 2008]
"SELECT login.coach_id, employee.emp_id, employee.coach_id, employee.emp_name, login.username, login.password FROM [login], [employee] WHERE login.coach_id = employee.coach_id"
How exactly should I include that when using ASP? Thanks!
Test your queries in Access to check that they work (you could use Access's query builder). Your login query's SQL would be something like (not tested):
strSql = "SELECT login.coach_id FROM [login] WHERE login.username ='" & yourFunctionToCleanInput(request("username")) & "' AND login.password = '" yourFunctionToCleanInput(request("password")) & "';"Where "username" and "password" are the names of your form fields, and "yourFunctionToCleanInput" is a function used to prevent SQL Injection by cleaning web input. The minimum cleaning you need to do is to replace single apostrophes with two single apostrophes.
Then with the login.coach_id, just query for employees for that ID:
strSql = "SELECT employee.emp_id, employee.coach_id, employee.emp_name, FROM [employee] WHERE employee.coach_id = " & intCoachID & ";"Where "intCoachID" is the variable used to store the ID retrieved with the login query.
I actually had to read your question a couple times until I realized that by
The way it works is the employee logs in to the site and then is able to view which new employees they are coaching.
you meant to say the coach logs in...not the employee logs in...correct?
