query definition - too complex for a newbie - please help

[swagtails]

Ive got a complex query that I cant get right...

The single table I need results from has the following elements :

staffcat1
staffcat2
staffcat3
area1
area2

The input varibales to the query are from 2 drop down lists - var1 for a staffcat & var2 for an area

The query has to satisfy :

(var1 = staffcat1 or staffcat2 or staffcat3) AND = (var2 = area1 or var2)

could some kind soul show me how the query should look please ?

Also, something that is getting me down, is how to check for a null value returned from a list menu & then return all records from from a recordset rather than follow the query ........ arggh I wish I could afford to go on a course !!

Cheers

[candelbc]

I'm sorry, I tried to make sense of your explanation and I can't quite figure out what you are trying to achieve.

I thought you might be interested in a free SQL tutorial that I use whenever someone is interested in learning..

Check it out at:

http://www.w3schools.com/sql/default.asp

Maybe if you can try to explain what you are after I'll be able to better post the SQL you need to use.

-Brad

[lmf232s]

dont sweat it. I have never taken an asp course, i learned from books.
I did go to college though where i did take some programming courses.
That did help build a foundation for programming, Logic, alogrithims, SQL statements and such. The only thing it did not help
me with is spelling:D .

Anyway buy a asp book, a javascript book, and a sql book.
Their are also great resources on the net. Here are a few.
Properties, methods, events etc for javascript, html, asp, etc.
http://www.devguru.com/home.asp

SQL and other good stuff
http://www.w3schools.com/sql/default.asp

http://www.asp101.com


SQL = "SELECT * FROM MYTABLE " & _
"WHERE staffcat1 = '" & var1 & "' " & _
" OR staffcat2 = '" & var1 & "' " & _
" OR staffcat3 = '" & var1 & "' " & _
" AND area1 = '" & var2 & "' "

THis is the best i could do with reading your talbe.
We say select * fields from your tables
where staffcat1 = var1
or staffcat2 = var1
or staffcat3 = var1
and area1 = var2

Hope that makes sence and you can take it from their.

[lmf232s]

sorry i posted befor i was done and i did not see
candelbc had already refered to one of the web sites that
i posted.

Just to let you know i did just use the web site
candelbc posted to find out how to do the OR statement.
It had been a while since i did that and was not sure.
I had it right but i did double check it.

[russell]

Those querries will return too many records.

should be

WHERE (
var1 = staffcat1
or var1 = staffcat2
or var1 = staffcat3
)
AND (var2 = area1
or var2 = area2
) With multiple logical operators (or, and not) the proper placing of the parentheses is critical

[swagtails]

Oh thanks Folks you are Fab !

I used Russells approach & it works like a dream, - so Cheers Russell & thanks to the rest..........

While I'm here though, the second part of my post asked this

I have 2 drop down lists which give the variables for a single search - neither of which is mandatory, but at least 1 must be present


How do I check for a null value in one of the variables so then only search on the other variable ?

eeekk ...

[russell]

sql = "SELECT fields FROM table "

If len(Request("someField")) Then
sql = sql & "WHERE someCondition"
End If

[russell]

Here's one way to do it if you want to check for the existence of either or both form variables and modify the query accordingly

Dim sql
Dim var1
Dim var2

var1 = Request("select1")
var2 = Request("select2")

sql = "SELECT fields FROM table WHERE 1=1"

If len(var1) Then
sql = sql & " And field1 = " & var1
End If

If len(var2) Then
sql = sql & " And field2 = " & var2
End If

[swagtails]

Hi Russell,

Think the light is dawning .... maybe

but can you tell me what the statement

"SELECT fields FROM table WHERE 1=1"

is doing ?

it's the "1=1" thats unclear to me

....so am I defining the sql as a variable and then appending to it depending on if there's something in var1 or var2 ?

[russell]

the where 1=1 is so that you don't have to keep checking to see if there is already a where clause when you start concatenating more conditions. 1=1 is always true, so it doesn't really do anything except give you a little less code to write

so am I defining the sql as a variable and then appending to it depending on if there's something in var1 or var2 ?
Exactly!

[swagtails]

So now I need to know how to apply this wizzardry to the code I already have.....which is

Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_jobsdb_STRING
Recordset1.Source = "SELECT * FROM staff WHERE ( staffcat1 = '" + Replace(Recordset1__MMColParam, "'", "''") + "' or staffcat2 = '" + Replace(Recordset1__MMColParam, "'", "''") + "' or staffcat3 = '" + Replace(Recordset1__MMColParam, "'", "''") + "' ) AND ( staffarea1 = '" + Replace(Recordset1__MMColParam2, "'", "''") + "' or staffarea2 = '" + Replace(Recordset1__MMColParam2, "'", "''") + "' )"


bearing in mind my brain cells are in danger of topping themselves !!

[russell]

Dim Recordset1
Dim Recordset1_numRows
Dim sql

'' build your sql here
'' like in the earlier post, sql = sql & "blah, blah, blah..."
'' then assign sql to recordset.source

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_jobsdb_STRING
Recordset1.Source = sql

[swagtails]

I shall try this tomorrow - my head is thumping with the effort ...

thanks for being so patient and helpful

swags

[swagtails]

Russell,

Well you really deserve your User Name.

After much messing & being stupid I got it to work as per your instructions - thanks so much.

If your ever in Worcester (UK) I'll buy you several beers :D

[russell]

Glad to help! I'll take you up on that next time i'm in the UK :)