Dreamweaver SQL Statements

[craiglb]

Hi, I am new to all this programming lark, so please forgive me in advance for any stupidly obvious questions i ask.

Iam currently building a database driven website for work - well trying to anyway
iam using dreamweaver MX and Access.

I have 3 tables in access :-

-Customers -
Company name
Comapny address
Telephone number
LocationID
ServiceID

-Service-
ServID
Service

-Location-
LocID
Location

I have put them together uisnt the relationship function i.e i have linked together ServiceID (in customers) with ServID and LocationID (in customers) with LocID

I think the above is correct - If now please tell me - thanx

My problems lye with my sql statement in the advanced record set. All i want to appear in the results is the companys details from the customers table and the location and serice form ther corrosponding tables.

Iam using The location and service to conduct the search, so i have 2 variables.

What would the sql statemant be to make this all come together and what do i put in my variables table.

Any help would be much appreaciated.

thanks

Craig

 

[deecee]

check out this site to help you with this

http://www.w3schools.com

Since you are learning its best you learn hands on rather than one of use spit out the code. When you have something post it and we will help [Hammer]
Nike Failed Slogans -- "Just Don't Do It!"

 

[craiglb]

Cheers. I have tried various statements but i still cant get them to work. the latest one being -

SELECT *
FROM Location, Service, Customers
WHERE LocID = 'varLocID' AND ServID = 'varServID' AND Location.LocID = Customers.LocationID AND Service.ServID = Customers.ServiceID


And for the variables i have -

-Name- -Default- -Run-time Value-
varServID (Dont Know) Request.form("Locatin&quot
varLocID (Dont Know) Request.form("Service&quot

If i put a default value in the search box it always appears in the search results, so i assume the search isnt pulling out any results from the database.

 

[deecee]

Try This

SELECT *
FROM Location, Service, Customers
WHERE Location.LocID = 'varLocID' AND Service.ServID = 'varServID' AND Location.LocID = Customers.LocationID AND Service.ServID = Customers.ServiceID [Hammer]
Nike Failed Slogans -- "Just Don't Do It!"

 

[craiglb]

The Sql works (I think) If i put in a default value (a value which exists in my database) then if i click test. It returns back the correct result from th database. If i leave the default value in, then search using my IIS. It always returns the default value. If i put the default value to % then no results are returned :-(

So the next question is why isnt my web page returning any results ?

A snippet from the code for the search options are -

<select name=&quot;Service&quot; size=&quot;8&quot; id=&quot;select2&quot;>

<option value=&quot;1&quot;>Accountant</option>

<option value=&quot;2&quot;>Electrician</option>

<option value=&quot;3&quot;>Plumber</option>

<option value=&quot;4&quot;>Joiner</option>

<option value=&quot;5&quot;>Painter & Decerator</option>




Any ideas?

 

[deecee]

u might want to try and union all the databases...

check out a union statement and see if this is best

because then the all you need to do is pass the varialbes on though your url ie

href=&quot;results.asp#locid=VARa&servid=VARb&quot;

where var stands for the inputs [Hammer]
Nike Failed Slogans -- &quot;Just Don't Do It!&quot;

 

[CTekMedia]

If you have null values in your table fields that can cause you to get no data. Using Access the easiest way to do this is to create a union query (as per Deecee's suggestion, but do it in Access (look at Union query under Access help). Once you get that worked out and your get the return you want from your query, then it will be easy to Select from that dynaset using php or asp or coldfusion or whatever. Breaking it into two parts like this makes it easier to troubleshoot. Peace

http://www.bluetone.biz

BT