OT: Need some SQL advice

[Tiemo Hollmann TB]

Hi,

I have only very basic SQL know how and am planning a new project, where I
need some SQL expertise before I start.

I have a db with 5 tables and about 20000 records in the main table, which
is read only (it's a kind of a dictionary).

I have 7 selection fields (say f1-f7) for selecting the datas and returning
the matching records (0-n).

There are 7 selection fields, which can all be used single or in any
combination to do the query, after each selection the resulting records are
shown immediatly. So the user can refine his query step by step.

In 6 of the selection fields the user has to select a distinct option (say
one out of 1,2,3,4,5,6)

But with one selection field (f7), the user can choose 0-n options out of 17
options (example: "1" or "2" or "9" or "17")

 

If I start with my basic SQL knowledge (reduced form):

SELECT * FROM myTable WHERE

f1=s1

AND f2=s2

AND f3=s3

...

AND f7=s7

                

now my questions:

1. How do I design one generic SQL SELECT statement, which fits for all
queries, where I only have to feed the variables with the options.

Example: if the user uses only the selection in field 1 (and leaves all
other selection on wildcard) it is much faster to query only field 1:

WHERE f1=s1

instead of:

WHERE f1=s1

AND f2=s2 (where I set s2="%")

AND f3=s3 (where I set s3="%")

...

AND f7=s7 (where I set s7="%")

But with this approach I can't setup one generic SQL statement which fits
for all queries. I would have to create douzends of SQL statements with all
possible combinations of selecting fields and would have to select the
actual SQL depending on the selection in my program. this isn't probably the
way of choice!? So how to do?

 

2. How do you design "subsequent" queries to speed up the performance? If
the user first selects an option in search field 1 I get a number of
matching records.

If now the user selects an option from search field 2 to narrow the search I
would like to query only the resulting records from the first search instead
of goign onto the full db with both search criterias. Can I handle this just
in memory (if yes how?) and leave my db a read only file or would I have to
create some kind of resulting fields in my table and write back the
resulting records from first search?

If the user deselects one of the combined search criterias I have to query
again the full db.

Or are these completely unnessesary thoughts with 20000 records and I should
go with every query on the full db?

 

 

3. For my field 7 with the OR options I would use the "IN" clause. right? Or
is there a more elegant/faster way?

 

Thanks for any coaching

Tiemo

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20101129/32a3877d/attachment.htm>