Working SQL way too long

[havey]

Can this sql somehow be sortened? SQL = "SELECT * FROM logbook WHERE rname IN ("
AFRICAN = Request.Form("AFRICAN")
If AFRICAN = "true" Then
SQL = SQL & "'AFRICAN',"
End If
CARIBBEAN = Request.Form("CARIBBEAN")
If CARIBBEAN = "true" Then
SQL = SQL & "'CARIBBEAN',"
End If
CHINESE = Request.Form("CHINESE")
If CHINESE = "true" Then
SQL = SQL & "'CHINESE',"
End If
CONTINENTAL = Request.Form("CONTINENTAL")
If CONTINENTAL = "true" Then
SQL = SQL & "'CONTINENTAL',"
End If
DUTCH = Request.Form("DUTCH")
If DUTCH = "true" Then
SQL = SQL & "DUTCH',"
End If
EAST_INDIAN = Request.Form("EAST_INDIAN")
If EAST_INDIAN = "true" Then
SQL = SQL & "'EAST_INDIAN',"
End If
ENGLISH = Request.Form("ENGLISH")
If ENGLISH = "true" Then
SQL = SQL & "'ENGLISH',"
End If
ETHIOPIAN = Request.Form("ETHIOPIAN")
If ETHIOPIAN = "true" Then
SQL = SQL & "'ETHIOPIAN',"
End If
FONDUES = Request.Form("FONDUES")
If FONDUES = "true" Then
SQL = SQL & "'FONDUE',"
End If
FRENCH = Request.Form("FRENCH")
If FRENCH = "true" Then
SQL = SQL & "'FRENCH',"
End If
FUSION = Request.Form("FUSION")
If FUSION = "true" Then
SQL = SQL & "'FUSION',"
End If
GERMAN = Request.Form("GERMAN")
If GERMAN = "true" Then
SQL = SQL & "'GERMAN',"
End If
GREEK = Request.Form("GREEK")
If GREEK = "true" Then
SQL = SQL & "'GREEK',"
End If
ITALIAN = Request.Form("ITALIAN")
If ITALIAN = "true" Then
SQL = SQL & "'ITALIAN',"
End If
JAPANESE = Request.Form("JAPANESE")
If JAPANESE = "true" Then
SQL = SQL & "'JAPANESE',"
End If
JEWISH = Request.Form("JEWISH")
If JEWISH = "true" Then
SQL = SQL & "'JEWISH',"
End If
KOREAN = Request.Form("KOREAN")
If KOREAN = "true" Then
SQL = SQL & "'KOREAN',"
End If
MEDITERRANEAN = Request.Form("MEDITERRANEAN")
If MEDITERRANEAN = "true" Then
SQL = SQL & "'MEDITERRANEAN',"
End If
MEXICAN = Request.Form("MEXICAN")
If MEXICAN = "true" Then
SQL = SQL & "'MEXICAN',"
End If
MONGOLIAN = Request.Form("MONGOLIAN")
If MONGOLIAN = "true" Then
SQL = SQL & "'MONGOLIAN',"
End If
PAKISTANI = Request.Form("PAKISTANI")
If PAKISTANI = "true" Then
SQL = SQL & "'PAKISTANI',"
End If
PHILIPPINE = Request.Form("PHILIPPINE")
If PHILIPPINE = "true" Then
SQL = SQL & "'PHILIPPINE',"
End If
PIZZA = Request.Form("PIZZA")
If PIZZA = "true" Then
SQL = SQL & "'PIZZA',"
End If
PORTUGUESE = Request.Form("PORTUGUESE")
If PORTUGUESE = "true" Then
SQL = SQL & "'PORTUGUESE',"
End If
RIBS = Request.Form("RIBS")
If RIBS = "true" Then
SQL = SQL & "'RIBS',"
End If
STEAK_HOUSES = Request.Form("STEAK_HOUSES")
If STEAK_HOUSES = "true" Then
SQL = SQL & "'STEAK_HOUSES',"
End If
THAILAND = Request.Form("THAILAND")
If THAILAND = "true" Then
SQL = SQL & "'THAILAND',"
End If
UKRAINIAN = Request.Form("UKRAINIAN")
If UKRAINIAN = "true" Then
SQL = SQL & "'UKRAINIAN',"
End If
VEGETARIAN = Request.Form("VEGETARIAN")
If VEGETARIAN = "true" Then
SQL = SQL & "'VEGETARIAN',"
End If
VIETNAMESE = Request.Form("VIETNAMESE")
If VIETNAMESE = "true" Then
SQL = SQL & "'VIETNAMESE',"
End If
WEST_INDIAN = Request.Form("WEST_INDIAN")
If WEST_INDIAN = "true" Then
SQL = SQL & "'WEST_INDIAN'"
End If
If (DINE_IN = "true" AND DINE_OUT = "true") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_IN','DINE_OUT')"
elseIf (DINE_IN = "true" AND DINE_OUT = "false") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_IN')"
elseIf (DINE_IN = "false" AND DINE_OUT = "true") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_OUT')"
End If

[PeOfEo]

orderby something in the statement.
select x from y where z = a orderby z desc

[havey]

Hello and thanks for the reply i'm lost by your reply, maybe i was poor in explaining my situation, i'll try again.

And again thanks for teh reply as i'm am lost with it. I'm using a flash form and the checkbox values for Flash checkboxes are defaulted to false are have only a true or false set, this cannot be changed as that is the way Flash is, so no changing the name value, consider it a challenge.

All of the check boxes run independently.... (except for DINE_IN & DINE_OUT , i'll get to those later) How this works is the user selects what meal type they want and they can select more than one, then the user selects a dine_in or/and dine out value.

In the db there are records where both dine_in and dine_out apply to the meal, so i included that db value into the selection(s). So basically what i'm doing is creating the sql where the first set of selection are true and where dine_in or/and dine_out are selected and pulling all info for those records, then i'm displaying them in a interactive pie chart in flash. The flash aspect i have no problem with its the asp.

I've managed to create this page but Flash only properly gets the values when the sql is "select * from logbook" another words when no selection is made, i'm thinking this is becasue the sql is too much of a hack job, needs to be more refined. This is my asp page (maybe there is some ?other? reason flash can only grab the value pairs when the sql = "select * from logbook" and i've response.writen the sql in this long version and it is like this format: sql = "select * from logbook WHERE cusine IN ('AFRICAN','GERMAN') AND eat IN ('BOTH','DINE_OUT')"



code:--------------------------------------------------------------------------------

DINE_IN = Request.Form("DINE_IN")
DINE_OUT = Request.Form("DINE_OUT")

Dim Serv, Conn, RecordView, Ename, output, collect, SQL, Eeat, Ephone, collectaa
Dim collectbb, collectdd , collect4, collectfg, collecthi, collect6, collect7, collectuu
Dim eoutput, eeoutput, e4output, e6eoutput, ae2output, eb7output, e7output, e9output, emoutput

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DBQ=" & Server.MapPath("\4d9b\db.mdb") & ";" & _
"DRIVER=Microsoft Access Driver (*.mdb)"

SQL = "SELECT * FROM logbook WHERE cusine IN ("
AFRICAN = Request.Form("AFRICAN")
If AFRICAN = "true" Then
SQL = SQL & "'AFRICAN',"
End If
CARIBBEAN = Request.Form("CARIBBEAN")
If CARIBBEAN = "true" Then
SQL = SQL & "'CARIBBEAN',"
End If
CHINESE = Request.Form("CHINESE")
If CHINESE = "true" Then
SQL = SQL & "'CHINESE',"
End If
CONTINENTAL = Request.Form("CONTINENTAL")
If CONTINENTAL = "true" Then
SQL = SQL & "'CONTINENTAL',"
End If
DUTCH = Request.Form("DUTCH")
If DUTCH = "true" Then
SQL = SQL & "DUTCH',"
End If
EAST_INDIAN = Request.Form("EAST_INDIAN")
If EAST_INDIAN = "true" Then
SQL = SQL & "'EAST_INDIAN',"
End If
ENGLISH = Request.Form("ENGLISH")
If ENGLISH = "true" Then
SQL = SQL & "'ENGLISH',"
End If
ETHIOPIAN = Request.Form("ETHIOPIAN")
If ETHIOPIAN = "true" Then
SQL = SQL & "'ETHIOPIAN',"
End If
FONDUES = Request.Form("FONDUES")
If FONDUES = "true" Then
SQL = SQL & "'FONDUE',"
End If
FRENCH = Request.Form("FRENCH")
If FRENCH = "true" Then
SQL = SQL & "'FRENCH',"
End If
FUSION = Request.Form("FUSION")
If FUSION = "true" Then
SQL = SQL & "'FUSION',"
End If
GERMAN = Request.Form("GERMAN")
If GERMAN = "true" Then
SQL = SQL & "'GERMAN',"
End If
GREEK = Request.Form("GREEK")
If GREEK = "true" Then
SQL = SQL & "'GREEK',"
End If
ITALIAN = Request.Form("ITALIAN")
If ITALIAN = "true" Then
SQL = SQL & "'ITALIAN',"
End If
JAPANESE = Request.Form("JAPANESE")
If JAPANESE = "true" Then
SQL = SQL & "'JAPANESE',"
End If
JEWISH = Request.Form("JEWISH")
If JEWISH = "true" Then
SQL = SQL & "'JEWISH',"
End If
KOREAN = Request.Form("KOREAN")
If KOREAN = "true" Then
SQL = SQL & "'KOREAN',"
End If
MEDITERRANEAN = Request.Form("MEDITERRANEAN")
If MEDITERRANEAN = "true" Then
SQL = SQL & "'MEDITERRANEAN',"
End If
MEXICAN = Request.Form("MEXICAN")
If MEXICAN = "true" Then
SQL = SQL & "'MEXICAN',"
End If
MONGOLIAN = Request.Form("MONGOLIAN")
If MONGOLIAN = "true" Then
SQL = SQL & "'MONGOLIAN',"
End If
PAKISTANI = Request.Form("PAKISTANI")
If PAKISTANI = "true" Then
SQL = SQL & "'PAKISTANI',"
End If
PHILIPPINE = Request.Form("PHILIPPINE")
If PHILIPPINE = "true" Then
SQL = SQL & "'PHILIPPINE',"
End If
PIZZA = Request.Form("PIZZA")
If PIZZA = "true" Then
SQL = SQL & "'PIZZA',"
End If
PORTUGUESE = Request.Form("PORTUGUESE")
If PORTUGUESE = "true" Then
SQL = SQL & "'PORTUGUESE',"
End If
RIBS = Request.Form("RIBS")
If RIBS = "true" Then
SQL = SQL & "'RIBS',"
End If
STEAK_HOUSES = Request.Form("STEAK_HOUSES")
If STEAK_HOUSES = "true" Then
SQL = SQL & "'STEAK_HOUSES',"
End If
THAILAND = Request.Form("THAILAND")
If THAILAND = "true" Then
SQL = SQL & "'THAILAND',"
End If
UKRAINIAN = Request.Form("UKRAINIAN")
If UKRAINIAN = "true" Then
SQL = SQL & "'UKRAINIAN',"
End If
VEGETARIAN = Request.Form("VEGETARIAN")
If VEGETARIAN = "true" Then
SQL = SQL & "'VEGETARIAN',"
End If
VIETNAMESE = Request.Form("VIETNAMESE")
If VIETNAMESE = "true" Then
SQL = SQL & "'VIETNAMESE',"
End If
WEST_INDIAN = Request.Form("WEST_INDIAN")
If WEST_INDIAN = "true" Then
SQL = SQL & "'WEST_INDIAN'"
End If
If (DINE_IN = "true" AND DINE_OUT = "true") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_IN','DINE_OUT')"
elseIf (DINE_IN = "true" AND DINE_OUT = "false") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_IN')"
elseIf (DINE_IN = "false" AND DINE_OUT = "true") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_OUT')"
End If

set RecordView = Conn.Execute(SQL)

Do While Not RecordView.EOF
Ername=RecordView("rname")
Eeat=RecordView("eat")
Ephone=RecordView("phone")
Eaddress=RecordView("address")
Ecusine=RecordView("cusine")
Ehours=RecordView("hours")
Especial=RecordView("special")
Eaddition1=RecordView("addition1")
Eaddition2=RecordView("addition2")
Ecrew=RecordView("crew")

collect =collect & Ername&","
collectaa =collectaa & Eeat&","
collectbb =collectbb & Ephone&","
collectdd =collectdd & Eaddress&","
collect4 =collect4 & Ecusine&","
collectfg =collectfg & Ehours&","
collecthi =collecthi & Especial&","
collect6 =collect6 & Eaddition1&","
collect7 =collect7 & Eaddition2&","
collectuu =collectuu & Ecrew&","

RecordView.MoveNext
Loop

output="&output="& collect
Response.Write output
Response.Write "end&"

eoutput="eoutput="& collectaa
Response.Write eoutput
Response.Write "end&"

eeoutput="eeoutput="& collectbb
Response.Write eeroutput
Response.Write "end&"

e4output="e4output="& collectdd
Response.Write eeoutput
Response.Write "end&"

e6eoutput="e6eoutput="& collect4
Response.Write eeoutput
Response.Write "end&"

ea2output="ea2output="& collectfg
Response.Write eeoutput
Response.Write "end&"

eb7output="eb7output="& collecthi
Response.Write eeoutput
Response.Write "end&"

e7output="e7output="& collect6
Response.Write eeoutput
Response.Write "end&"

e9output="e9output="& collect7
Response.Write eeoutput
Response.Write "end&"

emoutput="emoutput="& collectuu
Response.Write eeoutput
Response.Write "end&"

Counter = 1

Response.Write "Counter=" & Counter
Response.Write "&"

RecordView.Close
Set RecordView = Nothing
Set Conn = Nothing

[CardboardHammer]

As written, if user doesn't select 'WEST_INDIAN', then there's going to be a trailing "," in the set of values for the IN statement.

[havey]

any ideas to make this better? I still don't understand relating PeOfEo's solution.

[CardboardHammer]

There's nothing to understand of his... it wasn't actually an answer to your question.. I'll get back to this later...

[CardboardHammer]

Build your list of items for the IN in a separate string. At the end, remove the "," if it ends in one.

[havey]

that's what i'm confused about... i have two IN statements and the whole methodology is a bit beyong me, but none the less i'll give it a try, excuse me if i cross language by mistake.

So one of the IN statements:
Since I only need the check checkedboxes and since their value is defaulted to 'true' .... something like this:

for each "true" in request.form
if left(true.name,8)="cusine_" then
vars=vars & "'" & request(true) & "',"
end if
next
vars=left(vars,len(vars)-1)

Is that correct? It is suspose to give a comma seperated list of all the checkboxes selected.

the other IN statement:

for each "true" in request.form
if left(true.name,8)="eat_" then
vars2=vars2 & "'" & request(true) & "',"
end if
next
vars2=left(vars2,len(vars2)-1)

then the sql would be something like:

theSQL = "SELECT * FROM logbook where cusine IN (" & vars & ") AND eat IN (" & vars2 & ")"

[CardboardHammer]

Something like that... do a Response.Write to make sure you're getting what you want in the development stage.

[havey]

now that i think about it i can't do it that way cause the check box names are all different ... hmm i'm lost, any hints?

[CardboardHammer]

Start with what you had. Response.write to see your query. Figure out how to fix your query. Change your logic to match.

[PeOfEo]

Originally posted by CardboardHammer
There's nothing to understand of his... it wasn't actually an answer to your question.. I'll get back to this later...

I thought he was trying to sort from a data base. No use writing a bubble sort or something similar when it is already done for you. I guessed I missed the point of checkboxes, i was assumeing we were going to put it all in a data grid.

[CardboardHammer]

Ah, I understand where you were coming from now.

[PeOfEo]

I must have misread the first post or something

[havey]

Sorry I explaind things poorly, i find it hard to properly explain something i'm not good at.

I am looking to refine the sql. The checkboxes are in a Flash form and Falsh sets the default value of the checkboxes "false" and other option is 'true' i cannot change this to my own. I have a list of cuisines with check boxes and a seconday list of check boxes (eating style) consisting of:
dine in
dine out

either and both of these can be selected by the user.

Some of the records in the db have 'both' as a value for eating style. The flash value pairs created are like AFRICAN=true&CARIBBEAN=false&.....
I am only concerned with the cuisines where the value = true and the eating style where it is true as well.

I'm trying to create a SQL where cusines IN ('the selections that are true') AND eat IN ('BOTH', the selections that are true)

thanks for the replies!