OT: Need some SQL advice

[Ruslan Zasukhin]

On 11/29/10 1:18 PM, "Tiemo Hollmann TB" <toolbook at kestner.de> wrote:

Hi Tiemo,

> 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?

1)

Old way -- you just use string concats to buld STRING of query where needed
numbers are in place.

2) 

Better way to use SQL Binding

   = db.SqlSelect ( "...WHERE f1 = :1 AND f2 = :2    ...",  arrayOfParams )


V4RB examples do have such example
Please check it,


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]