SQL Syntax

Cinderella · July 19th, 2004, 03:52 PM

Hi guys,

I am writing a SQL statement to retrieve records of three tables, Employees, Users, and Branch. Employees and Users are joined on user_id; Users and Branch are joined on Users.addr1 (Branch.branch)

Below is my statement. It doesn't generate any error. However, I always got about 80 empty records when I applied "sort" function on the retrieved records.

sql = "SELECT " & _
    "Employees.employee_id, " & _
    "Employees.user_id, " & _
    "Branch.branch_phone, " & _
    "Users.l_name, " & _
    "Users.user_name, " & _
    "Users.f_name, " & _
    "Users.addr1, " & _
    "Users.email FROM (Employees LEFT JOIN (Branch LEFT JOIN Users ON Branch.branch = Users.addr1) ON Employees.user_id = Users.user_id)" & _
    " WHERE " & _
    "Employees.employee_id = " & to_sql(employee_id,"number") & ""

Please let me know whether there is any hidden "bug" in my statement. Thanks for your help!

Cinderella

Jonax · July 19th, 2004, 04:20 PM

I think you want INNER JOINS...

Code:

FROM Branch 
INNER JOIN Users 
ON Branch.branch = Users.addr1
INNER JOIN Employees 
ON Users.user_id = Employees.user_id

happygv · July 19th, 2004, 08:27 PM

Hi Cinderella,

Also you can try this alternatively.

Code:

sql = "SELECT Employees.employee_id, Employees.user_id, Branch.branch_phone, " &_
"Users.l_name, Users.user_name, Users.f_name, Users.addr1, " & _ 
"Users.email FROM Employees, Branch, Users " & _
"WHERE Branch.branch = Users.addr1 and Employees.user_id = Users.user_id " & _
"and Employees.employee_id = " & to_sql(employee_id,"number")

And you can remove this & "" at the end of your sql string, which is required only when one needs to add a single quote around the employee_id like this & "'", but in your case, not required as it seems to be a number.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Cinderella · July 21st, 2004, 01:06 PM

Hi guys,

Thanks a lot! Problem solved!

Cinderella