Writing A SQL Statement

[victoria_1018]

Hi,

I am trying to wrirte a SQL statement for one of my query page in my page.

This SQL statement will do select from table WorksOrder and staff.

The staff will submit a company name.
The system will do a search, loop through the whole WorksOrder table to look for records with this company's name, WorksOrder Status = "Not Approved", and the staff's name on the selected records is equals to the staff's Login ID.

While writing this SQL statement, I had used INNERE JOIN but it don't work.

The SQL Statement is

sql = "SELECT * FROM WorksOrder INNER JOIN Staff ON (WorksOrder.KCStaff = Staff.LoginID) WHERE Company like '"& SearchCustomer &"%' AND Status = 'Not Approved'"

Thank You

Regards
Victoria

[BigDaddy]

sql = "SELECT * FROM WorksOrder INNER JOIN Staff ON (WorksOrder.KCStaff = Staff.LoginID) WHERE Company like '"& SearchCustomer &"%' AND Status = 'Not Approved'"


Try:


sql = "Select * from WorksOrder w, Staff s where w.KCStaff = s.LoginID and w.Company like '" & SearchCustomer&"%' and w.Status = 'Not Approved'"

That's assuming that Company and Status are part of the WorksOrder table.

Are you getting any type of an error? If so, what?

Or is it not returning any values? It could be that perhaps the fields you're comparing, like WorksOrder.KCStaff and Staff.LoginID are not the same if you don't get anything back in the recordset.

[victoria_1018]

HI Big Daddy,

After testing your SQL statement on my program, the script run without any any error and display all records where status equals to not approved. However, All these records that are displayed are not group according to the user, which is the w.KCStaff =s.LoginID did not works.

I had run through the script and did not know where goes wrong.

Is there another way to write the SQL statement?

Regards
Victoria:confused:

[Roy Sinclair]

First off, you shouldn't be crossposting the same question to two different forums. Your post count is high enough that you ought to know better by now.

If you want the records to come back in a specific order then you need to add the "ORDER BY" clause to your SQL statement followed by the names of the fields you want the records to be sorted on.

sql = "Select * from WorksOrder w, Staff s where w.KCStaff = s.LoginID and w.Company like '" & SearchCustomer&"%' and w.Status = 'Not Approved' ORDER BY w.Company, s.KCStaff"

Would order the records by Company and KCStaff within company.

[victoria_1018]

Dear Roy Sinclair,
I am sorry about the crossposting.

The SQL Statement that you had provided me is not displaying how I want the result to be arranged. After trying for two days and I still could not get the result and dicided to approach you again.

This is the SQL Statement you provided me in the last reply

sql = "SELECT * from WorksOrder w, Staff s where w.KCStaff = s.LoginID and w.Company like '"& SearchCustomer&"%' and w.Status = 'Not Approved' ORDER By w.Company, s.LoginID"

My question is if the user is logged into the system using Mary as her login ID, can the system just display records by Mary. In short, the SQL Query will do a select from WorksOrder table and Staff table where where WorksOrder.KCStaff = Staff.LoginID=Mary ?

Thank You

Regards
Victoria

[ReyN]

hello Victoria

if you use INNER JOIN, there must be a common field for the tables to join, because this is where you relate the tables. in other words what is the field common to both WorksOrder and Staff tables?

basically, your INNER JOIN syntax should be:

sql = "SELECT * FROM WorksOrder INNER JOIN Staff ON WorksOrder.commonField = Staff.commonField WHERE Company like '" & SearchCustomer & "%' AND Status = 'Not Approved'"

hth :D

[Roy Sinclair]

Originally posted by victoria_1018
Dear Roy Sinclair,
I am sorry about the crossposting.

The SQL Statement that you had provided me is not displaying how I want the result to be arranged. After trying for two days and I still could not get the result and dicided to approach you again.

This is the SQL Statement you provided me in the last reply

sql = "SELECT * from WorksOrder w, Staff s where w.KCStaff = s.LoginID and w.Company like '"& SearchCustomer&"%' and w.Status = 'Not Approved' ORDER By w.Company, s.LoginID"

My question is if the user is logged into the system using Mary as her login ID, can the system just display records by Mary. In short, the SQL Query will do a select from WorksOrder table and Staff table where where WorksOrder.KCStaff = Staff.LoginID=Mary ?

Thank You

Regards
Victoria

To search only for records by the current logged on user you need the logged on user's name and a clause in the SQL to restrict the records returned to it. Something like this possibly:


sql = "SELECT * from WorksOrder w, Staff s
where w.KCStaff = s.LoginID
and w.Company like '"& SearchCustomer&"%'
and w.Status = 'Not Approved'
and s.LoginID = '" & Request.ServerVariables("LOGON_USER") & "'
ORDER By w.Company, s.LoginID"

[victoria_1018]

Hi Roy Sinclair

'" & Request.ServerVariables("LOGON_USER") & "'

Regarding to the above codes in red, is this code retrieving the user LoginID for logging into the system or my program.

For Example,
If I login to the system using victoria.o and victoria to login into this program. When I do I search where w.KCStaff = s.LoginID, will it display result for victoria.o or victoria?

Another thing is for every page that I had design, I had put this codes before HTML tag to find out if the user had logged into areas where they can access.

<%
if session("staffID") = "" then
Response.Redirect "../Login.asp"

end if
%>


Thank You

Regards
Victoria

[Roy Sinclair]

The code I placed will pick up the user name of the logged in user as long as the web server is set up to require a logged in user. If you already have the user's name in a session variable then it would be better to use that existing variable instead.

[victoria_1018]

Hi Roy Sinclair,
I have solve my problem.
Thank You
Victoria