SQL Query Question

sql, mulitple tables, ID

[b_marks]

Hi,
I'm having some issues with a site I'm making for employees to manage the mentoring or coaching of new employees. The way it works is the employee logs in to the site and then is able to view which new employees they are coaching. So I set up two tables in a databaase in Access - one for the coaches with the login information and another for the new employees that are assigned to them. The new employees are assigned to their coach through the ID field of the first table. So my columns and tables look like this:

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!

[LifeinAsia]

It depends on what you're using for your application (PHP, ASP, etc.- this example is for Cold Fusion), but something like this should work:
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#'

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]

[b_marks]

Thanks for the feedback. I'm using ASP and here is the query:

"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!

[LifeinAsia]

I don't know ASP very well. Instead of # signs, use whatever symbol ASP uses to denote form variables.

[mattur]

Split your query into 2 queries: first login and then select employees for a coach. By only using one query you can't tell the difference between a coach with no employees assigned and an incorrect login; they both just return an empty recordset.

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.

[Clark]

Exactly. Your first query was completely wrong because it didn't limit the results to the id of the coach.

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?