PDA

View Full Version : Re: Advanced SQL Quiry


malhyp
12-07-2005, 08:40 AM
Hiya to all.

Can anyone suggest where I would find a SQL crash course. I have currently created a site with a Access database. In DWMX04 I have created an advanced Recordset. In this recordset I have created a name, selected the database and then copied the SQL from access to the SQL section. My SQL reads like the following.
-----------------------------------------------------------------------------------------------
SELECT [tblSpecies].[Fruit Species], [tblSuppliers].[Supplier Name], [tblSuppliers].[Contact Name], [tblSuppliers].[Street Address], [tblSuppliers].[Suburb], [tblLocation].[Location], [tblSuppliers].[Post Code], [tblCountry].[Country], [tblSuppliers].[Phone Number], [tblSuppliers].[Facsimile], [tblSuppliers].[Email Address], [tblSuppliers].[Website Address], [tblSuppliers].[Short Description], [tblSuppliers].[Description], [tblSuppliers].[Image], [tblCategories].[CategoryTitle]
FROM tblSpecies INNER JOIN (tblCategories INNER JOIN ((tblLocation INNER JOIN (tblCountry INNER JOIN tblSuppliers ON [tblCountry].[idCountry]=[tblSuppliers].[idCountry]) ON [tblLocation].[idLocation]=[tblSuppliers].[idLocation]) INNER JOIN tblResults ON [tblSuppliers].[idSupplier]=[tblResults].[idSupplier]) ON [tblCategories].[idCategory]=[tblResults].[idCategory]) ON [tblSpecies].[idSpecies]=[tblResults].[idSpecies];
------------------------------------------------------------------------------------------------
I dont have anything in in the Variables section nor do I have anything selected in the Database Items section.
When I search the database, I get results of all the listings I have on the database.

Can anyone suggest where I have gone wrong.

Sorry very new to SQL.

Thanks to all.

Mally.

davidj
12-07-2005, 08:52 AM
you have asked this before and i said that you dont have a WHERE clause so you are going to get every record back

Creative Insanity
12-07-2005, 09:43 AM
Yeah.. you are going SELECT which is just what it is going to do.. like dj said you need a where. IE select where [blah]
I will answer better tomorrow as it is getting late and she is damn well shouting at me again.

malhyp
12-07-2005, 10:41 AM
Hey, this is my 1st post re SQL. Please explain in a little more detail.

If it helps in my search page I have 3 fields that people can search in.

key_word

category

location.

Any help would be greatly appreciated.

Mally.

davidj
12-07-2005, 10:59 AM
ok
SQL statement should be...

select fields etc from table // this is what you have

select fields etc from table WHERE field = 'your criteria' // this is what it should be

//-----------------


this statement looks in table1 and gets fields1 -3 then asks to show records where field1 = 21, field2 = 'aa' and field3 is like dav (will come back with results such as dav, david, DaVinci etc)

select field1, field2, field3 from table1 where field 1 = '21' or field2 = 'aa' and field3 LIKE 'dav%'

davidj
12-07-2005, 11:12 AM
in addition

i notice that your searching on 3 fields but you are showing every field in your SQL
You can use * to show all

example...

select * from table where field = '123'