logic help please 1

[derwent]

I'm stuggling trying to suss the logic out for a search facility I need to create for our new website, can someone look over this please?

I have a database containing our companies properties. Some are for sale, some rent and some part purchase. So I have these on the search to be selected, the user should be able to select one, multiple or none. If they select one, then only that table is searched, if multiple those tables are searched, if none then all tables are searched.

Each of the above categories are also broken down into sub categories of social, city, retirement etc. Again these need to be checkbox's allowing one, multiple or none to be selected.

HELP!!

 

[theniteowl]

Well, the easiest thing to do is have one query for each table you might search. You test to see if a specific checkbox was checked and if it was execute the query to retrieve the related data then move on to the next test.
It is possible to link your tables in one query to get it all at once but it would be quite a complex query to build on the fly.
As for breaking down into sub categories, that is easy from a query perspective. The big question becomes how are handling the data once you pull it from the database?

MyQuery = "Select * From myTable Where "
If Option1 = True Then
MyQuery = MyQuery & "Option1 = '" & mycompare1 & "'"
End If
If Option2 = True Then
If MyQuery = "Select * From myTable Where " Then
MyQuery = MyQuery & ", "
End If
MyQuery = MyQuery & "Option2 = '" & mycompare2 & "'"
End If
If Option3 = True Then
If MyQuery = "Select * From myTable Where " Then
MyQuery = MyQuery & ", "
End If
MyQuery = MyQuery & "Option3 = '" & mycompare3 & "'"
End If

This is quick and crude but shows how you can build your query string based on values you passed in.

What logic specifically are you having trouble with?


Paranoid? ME?? WHO WANTS TO KNOW????

 

[DotNetGnat]

ok you have 3 tables here...

1.Sale
2.Rent
3.Purchase

i would then go ahead and create a view or a temp table using something like this...

Create table mytemptable(field1 int,
field2 varchar(10), field3 varchar(10), flag int)

Insert Into mytemptable
SELECT Field1, Field2, Field3,1 FROM Sale
UNION ALL
SELECT Field1, Field2, Field3,2 FROM Rent
UNION ALL
SELECT Field1, Field2, Field3,3 FROM Purchase

Hope you got that straight...now if the user selects the one table(lets say Sale) then you do...

Select * from mytemptable where flag=1

on the other hand if the user selects all the 3 tables then you can do...

Select * from mytemptable where flag IN (1,2,3)


ok thats the step 1...

now to deal with in the table content itself...depending on what table the user selected you can modify your query...

hope that helps...

post back if you have more questions...

-DNG