Searching exact values with SQL

Question

I have this kind of table:

I want to get a "a" value such that it has the exact searched values for "b". For example, if I search for (7,2), I want the query to return 8, if I search for (7,2,3), I want it to return 9, otherwise it must return Null:

Search      Result
(7,2)       8
(7,2,3)     9
(7,3)       Null
(7,2,4)     Null
...

Is it possible to do it without using the "group by" concatenation?

[Edit] For "group by concatenation" I mean something like the GROUP_CONCAT() in MySql or however any string functions.

does the order matter? in other words, are you looking to find the matching "sequence"? Here's what I mean: in your example, you have (7,2,3) results in 9. What should (2,7,3) result in? — Jeremy Danyow, Mar 27 '14 at 11:17
You should explain why "group by" isn't appropriate, since it looks like most solutions will include it. This is not http://codegolf.stackexchange.com/ You should also explain what you have tried so far, and given that you are apparently searching with a variable length parameter list, it would help for you to indicate how you intend to invoke that search. — perfectionist, Mar 27 '14 at 11:35
Can't think of an accurate title for this question or i'd post an edit but "search for exact values in sql" is very misleading. Any ideas? — perfectionist, Mar 27 '14 at 11:37

score 0 · Answer 1 · answered Mar 27 '14 at 11:09

0

The group by/concatenation method works. Another method is group by with a having clause:

select a
from table t
group by a
having sum(case when b = 7 then 1 else 0 end) = 1 and
       sum(case when b = 2 then 1 else 0 end) = 1 and
       sum(case when b not in (7, 2) then 1 else 0 end) = 0;

The idea is to count the values that match in each value. You can actually do this with string operations, but those differ among databases. Here is a "general" way of doing this using in and the number of values you are looking for:

select a
from table t
group by a
having count(*) = 2 and
       count(distinct (case when b in (2, 7) then b end));

answered Mar 27 '14 at 11:09

Gordon Linoff

1,242,037
58
646
786

Actually the OP asked for a solution *without* group by. – Fabian Bigler Mar 27 '14 at 11:14
`and count(distinct (case when b in (2, 7) then b end))` does seem to be a proper condition – Mar 27 '14 at 11:24
@FabianBigler . . . The user asked for a solution without "group by concatenation". – Gordon Linoff Mar 27 '14 at 11:31

Mudassir Hasan · Answer 2 · 2014-03-27T11:34:35.647

0

In SQL Server

SELECT a FROM tableName
WHERE a NOT IN ( SELECT a FROM (   
                               SELECT a,
                               CASE WHEN CHARINDEX(b,@searchTerm)=0 
                               THEN 0 ELSE 1 END as Result
                               FROM tableName
                               )z
                  WHERE z.Result=0
               )

I am using CHARINDEX() function to check whether search term exist.

edited Mar 27 '14 at 11:34

answered Mar 27 '14 at 11:10

Mudassir Hasan

28,083
20
99
133

using `charindex` does not make any sense for a numeric column with just a single value in it. – Mar 27 '14 at 11:45

score 0 · Answer 3 · answered Mar 27 '14 at 11:29

Not sure what you mean with "group by concatenation" - I don't think it can be done without a group by:

You didn't specify your DBMS so this is ANSI SQL:

with search_values (val) as (
   values (7), (2)
)
-- first part gets all those that do have the search values
-- but will also include those rows that have more than the searched ones
select a
from data
where b in (select val from search_values)
group by a
having count(distinct b) = (select count(*) from search_values)

intersect
-- the intersect then filters out those that have exactly the search values

select a
from data
group by a
having count(distinct b) = (select count(*) from search_values);

SQLFiddle example: http://sqlfiddle.com/#!15/dae93/1

Using a CTE for the "search values" avoids repeating them and and avoids "hardcoding" the number of items to be searched. If you look for 7,2,3 you just add another value to the CTE

Instead of using intersect it can be rewritten with a co-related subquery

with search_values (val) as (
   values (7), (2)
)
select d1.a
from data d1
where d1.b in (select val from search_values)
group by d1.a
having count(distinct d1.b) = (select count(*) from search_values)
   and count(distinct d1.b) = (select count(*) from data d2 where d2.a = d1.a);

Fabian Bigler · Answer 4 · 2014-03-27T12:32:16.420

Another way would be to use INTERSECT.

However, the first case seems to be a nut which still needs to be cracked (returns 8,9 instead of 8).

Here's the SQL Code:

--(7,2) => 8,9 (instead of 8) :(
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 7
   INTERSECT
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 2

--(7,2,3) => 9
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 7
   INTERSECT
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 2
   INTERSECT
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 3

--(7,4) => NULL
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 7
   INTERSECT
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 4

--(7,2,4) => NULL
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 7
   INTERSECT
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 2
   INTERSECT
   SELECT  distinct(a)
    FROM   t1
    WHERE  b = 4

Fiddle Demo

Searching exact values with SQL

4 Answers4