Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 19th, 2004, 03:52 PM
Authorized User
 
Join Date: Jun 2004
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Syntax

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

 
Old July 19th, 2004, 04:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 184
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jonax
Default

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
 
Old July 19th, 2004, 08:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old July 21st, 2004, 01:06 PM
Authorized User
 
Join Date: Jun 2004
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi guys,

Thanks a lot! Problem solved!

Cinderella






Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help with SQL syntax DeannaF829 VB Databases Basics 2 April 30th, 2007 12:12 PM
SQL-syntax grstad SQL Language 9 January 30th, 2007 02:40 PM
syntax of sum in SQL heghtera Access VBA 1 March 10th, 2006 03:17 AM
SQL Syntax yves SQL Server 2000 6 February 2nd, 2006 08:26 PM
SQL Syntax jeffg22 SQL Language 6 July 28th, 2003 06:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.