3 table join structure 2

[btween]

I have 3 tables that I need to combine. When I use an inner join this works for 2 tables but when I try to add the 3rd table the result set is inexistant.

This is my table structure

tblEmployees
empid (primary, autonumber)
.
.


the 2 related tables:

tblEmployeeAssets
empassetsid (primary, autonumber)
empid (foreign key)
.
.
.

tblEmployeeStats
statsid (primary, autonumber)
empid (foreign key)
.
.
.


How can I produce a result set that returns employees that match criteria in each table?

thanks for your help

 

[PHV]

Seems that either Assets or Stats are not mandatory for Employees, so take a look at outer joins:
SELECT your fields list here
FROM (tblEmployees E LEFT JOIN tblEmployeeAssets A ON E.empid = A.empid)
LEFT JOIN tblEmployeeStats S ON E.empid = S.empid

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

 

[btween]

OK,

I tried this but I am getting an error.

when I do a response.write on the strSQL this is what it produces:

SELECT Employees.emp_id, emp_first_name, emp_last_name, emp_phone_1, emp_phone_2, emp_cellphone FROM (Employees E LEFT JOIN Employee_Assets A ON E.emp_id = A.emp_id) LEFT JOIN Employee_Stats S ON E.emp_id = S.emp_id WHERE emp_last_name LIKE '%a%' AND emp_first_name LIKE '%%' AND emp_address LIKE '%%' AND emp_city LIKE '%%' AND emp_state LIKE '%%' AND emp_zip LIKE '%%'

this is the erro that is produced.

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/empresults.asp, line 138

 

[PHV]

Double check the correct spelling of each field/table name.

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

 

[btween]

the spelling is correct, there is something wrong with the clause.

 

[PHV]

Try to replace this:
SELECT Employees.emp_id
By this:
SELECT E.emp_id

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

 

[traingamer]

if you're using Access, the wildcard character is '*' as opposed to '%' for SQL. (Which I'm sure you already knew.)

Most of your 'where' clause is unneeded.
You just need

WHERE emp_last_name LIKE '%a%'

or

WHERE emp_last_name LIKE '*a*'

For testing purposes, I'd try the select without a 'where' clause to see if that is where the problem lies.

traingamer