<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" xmlns:Repl="http://schemas.microsoft.com/repl/" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda="http://www.passport.com/NameSpace.xsd" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.E-MailFormatvorlage17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=DE link=blue vlink=purple>
<div class=WordSection1>
<p class=MsoNormal><span lang=EN-US>Hi,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>I have only very basic SQL know how and am
planning a new project, where I need some SQL expertise before I start.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>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).<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>I have 7 selection fields (say f1-f7) for
selecting the datas and returning the matching records (0-n).<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>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.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>In 6 of the selection fields the user has
to select a distinct option (say one out of 1,2,3,4,5,6)<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>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")<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US>If I start with my basic SQL knowledge
(reduced form):<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>SELECT * FROM myTable WHERE<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>f1=s1<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>AND f2=s2<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>AND f3=s3<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>...<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>AND f7=s7<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>now my questions:<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>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.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>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:<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>WHERE f1=s1<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>instead of:<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>WHERE f1=s1<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>AND f2=s2 (where I set s2="%")<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>AND f3=s3 (where I set s3="%")<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>...<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>AND f7=s7 (where I set s7="%")<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>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?<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US>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.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>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?<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>If the user deselects one of the combined
search criterias I have to query again the full db.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US>Or are these completely unnessesary
thoughts with 20000 records and I should go with every query on the full db?<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US>3. For my field 7 with the OR options I
would use the "IN" clause. right? </span>Or is there a more
elegant/faster way?<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Thanks for any coaching<o:p></o:p></p>
<p class=MsoNormal>Tiemo<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</body>
</html>