SQL : Exclusive search over multiple columns

Kai

Howdy i have a database table like this:

[HTML]<table>
<tr>
<td height=17 class=xl24 width=64 style='height:12.75pt;width:48pt'>Login ID</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>Name</td>

<td class=xl24 width=64 style='border-left:none;width:48pt'>Sect</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SN 1</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SL 1</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SN 2</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SL 2</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SN 3</td>

<td class=xl24 width=64 style='border-left:none;width:48pt'>SL 3</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SN 4</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SL 4</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SN 5</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SL 5</td>
<td class=xl24 width=64 style='border-left:none;width:48pt'>SN 6</td>

<td class=xl24 width=64 style='border-left:none;width:48pt'>SL 6</td>
</tr>
<tr>
<td>1234</td>
<td>Kai</td>
<td>9</td>
<td>35</td>

<td>8</td>
<td>14</td>
<td>8</td>
<td>12</td>
<td>44</td>
<td>8</td>

<td>1</td>
<td>2</td>
<td>3</td>
<td>4</td>
<td>5</td>
</tr>

</table>[/HTML]

From a Web application id like to be able to perform an exclusive search. The webpage has 3 text boxes where we can enter the SN value we are looking for. Say i entered 14 and 35 in two of the boxes and clicked search. Id like to get back any person with ONLY 14 and 35 in any SN column and Null in all the rest. So basically all the people who have 14 and 35 in any SN and nothing else.

Ive managed to get this working for 3 of the SN columns but it involved a WHERE statement like this :

_str &= " where ("
            _str &= " ([SN 1]" & text1 & " AND [SN 2]" & text2 & " AND [SN 3]" & text3 & ") " & Environment.NewLine
            _str &= " OR ([SN 1]" & text1 & " AND [SN 2]" & text3 & " AND [SN 3]" & text2 & ") " & Environment.NewLine
            _str &= " ) OR (" & Environment.NewLine

            _str &= " ([SN 1]" & text2 & " AND [SN 2]" & text1 & " AND [SN 3]" & text3 & ") " & Environment.NewLine
            _str &= " OR ([SN 1]" & text2 & " AND [SN 2]" & text3 & " AND [SN 3]" & text1 & ") " & Environment.NewLine
            _str &= " ) OR (" & Environment.NewLine

            _str &= " ([SN 1]" & text3 & " AND [SN 2]" & text1 & " AND [SN 3]" & text2 & ") " & Environment.NewLine
            _str &= " OR ([SN 1]" & text3 & " AND [SN 2]" & text2 & " AND [SN 3]" & text3 & ") " & Environment.NewLine
            _str &= " ) " & Environment.NewLine

It has to work over 6 text boxes and 6 columns now so i want to see if theres an easier way.

i changed the text variables to be "= VALUE" or "Is Null" earlier in the code to make this part easier.


So anyone got any ideas for a cleaner way to do this. Either on the ASP.Net side or on the SQL query?

EDIT: Anyone know how to get the html code above to show as a table on this page?

bonkey

My immediate reaction would be that the easier way is to redesign the DB to be properly relational, but I'm guessing thats not an option...

...hmmm...

I don't suppose there's any useful additional information, e.g that the SN fields are filled from left to right (i.e. if there are two values, only SN1 and SN2 will be filled, and not any arbitrary pair) ???

If not...you may well just be buggered.

rsynnott

SQL Injection...

Kai

rsynnott wrote:

SQL Injection...

Any thing useful to say regarding my query ? Its drawing from an access database that gets deleted and recreated every 15 mins. The only people who have access to this page is a small team of 5 people. Even if they deleted the entire database it would have no effect on anything important. Im well aware of SQL Injections and take measures to counteract them when Neccessary.

Fair enough ill find a work around so if no one has any ideas. Thanks.

rsynnott

Kai wrote:

Any thing useful to say regarding my query ? Its drawing from an access database that gets deleted and recreated every 15 mins. The only people who have access to this page is a small team of 5 people. Even if they deleted the entire database it would have no effect on anything important. Im well aware of SQL Injections and take measures to counteract them when Neccessary.

Fair enough ill find a work around so if no one has any ideas. Thanks.

As long as it's not accessible from the outside and people are trust-able, it's fine. As you said web application, though, I thought it as well to point it out, in case you didn't know.

You could simply build long unpleasant queries with a loop, but I'm sure there's a nicer way.

bonkey

I had a think about this over the weekend, and there may be a cheeky way of doing it.

Consider this...

I can create a calculated field telling me how many NULL values are stored between SN1 to SN6:

case when SN1 is null then 0 else 1 end
+ 
case when SN1 is null then 0 else 1 end
+ 
...
+ case when SN6 is null then 1 else 0 end

I know how many values the user is searching for, so I know how many null values I want, correct? If 2 values are supplied, I want 4 null values.

So....for two entered values, I'd build an SQL statement which was something like :

WHERE <first supplied val> IN (SN1, SN2, SN3, SN4, SN5, SN6)
  AND   <second supplied val> IN (SN1, SN2, SN3, SN4, SN5, SN6)
  AND  4 = case when SN1 is null then 0 else 1 end
         + case when SN1 is null then 0 else 1 end
         + ...
         + case when SN6 is null then 1 else 0 end

This statement can easily be built dynamically using some simple logic that will give me N versions of the IN comparison, where N is the number of entered values, and where the LHS value of the last comparison is generated as (6-N).

I'm not 100% sure if CASE is supported in MSAccess queries, but if it isn't, there's definitely an IF or some equivalent.

The only place such a query could fall down is if its possible to have the same value stored in two seperate SNs...

jc

Kai

Hi bonkey, thats looks really promising, theres no case statement but i can use and IIF i think. Ill give it a go and see what i get.

bonkey

Iif(IsNull([SN1]), 1, 0) + ...

Kai

Worked like a charm, i had to use isnumeric rather than isnull but its running perfectly now. Thanks for that lad.