There is a table

id | hash | name_var | value 1 a number 5 2 a text hello 3 b number 5 4 b text goodbye 5 с number 6 6 с text apple 7 d number 6 8 d text orange

How to select all hash where number is 5 and hello is missing in text?

    5 answers 5

    In one reading, this is simply solved:

     SELECT hash FROM table GROUP BY hash HAVING --обязательно должна быть строка, где name_val='number' AND value='5' MAX(CASE WHEN name_val='number' AND value='5' THEN 1 ELSE 0 END) = 1 AND --не должно быть строки, где name_val='text' AND value='hello' MAX(CASE WHEN name_val='text' AND value='hello' THEN 1 ELSE 0 END) = 0

    And if some hash has no text property, the request will return this hash. And if the number property is missing, then such a hash will not return. As I understood it is exactly what is required

       select hash from table t1 where name_var='number' and value=5 and not exists(select 1 from table t2 where t1.hash=t2.hash and t2.name_var='text' and t2.value = 'hello')
      • +1: It should work correctly, but select hash should be replaced with select distinct hash to satisfy the condition "unique values" - Oleg
      • @Oleg Since the structure is not described, I assume that there should be no duplicate lines. Extra sorting is a loss of performance. So I will not put a distinction for reasons of principle. :-) - msi
       declare @t table (id int, [hash] varchar(5), [name_var] varchar(20), value varchar(20)) insert into @t(id,[hash], [name_var], [value]) values(1, 'a', 'number', '5'), (2, 'a', 'text', 'hello'), (3, 'b', 'number', '5'), (4, 'b', 'text', 'goodbye'), (5, 'с', 'number', '6'), (6, 'с', 'text', 'apple'), (7, 'd', 'number', '6'), (8, 'd', 'text', 'orange') select A.* from @t A INNER JOIN (select N.hash from @t N where N.[name_var] = 'number' and N.[value] = '5') B ON A.hash = B.hash where A.name_var = 'text' And A.value <> 'hello'

        Will fit?

         SELECT t1.hash FROM table t1 JOIN table t2 ON t1.hash=t2.hash WHERE t1.value=5 AND t1.name_var='number' AND t2.value<>'hello' AND t2.name_var='text' GROUP BY t1.hash
        • this is all one table - user193361
        • Well, the table itself is merged with itself .. If the table is called eta_tablitsa , then your query will look like this: SELECT t1.* FROM eta_tablitsa t1 JOIN eta_tablitsa t2 , etc. - cyadvert
        • Depending on what you need - you may need another GROUP BY - cyadvert
        • corrected the question - user193361
        • corrected the answer. - cyadvert

        For the case, if you need to find several equalities and several inequalities, you can use the following query (correctly for MySQL and Oracle, for other DBMS did not check):

         select hash from alldata T1 where (name_var,value) in (('number','5'),('abcd','xyz')) and not exists( select 1 from alldata T2 where T2.hash=T1.hash and (T2.name_var,T2.value) in (('text','hello'),('text','apple'),('param','2')) ) group by hash having count(1)=2

        This query will give all hash for which there are lines with number=5 И abcd='xyz' for which there is no record c: text='hello' или text='apple' или param!='2' .

        The number 2 in the line having count(1)=2 is the number of conditions that should match in the first part of the sql query. It must exactly match the number of different name_var mentioned in the first part of the request.

        In the first part, it is not allowed to specify in in checking the same field for several different values. If this occurs, then in, written for convenience, must be deployed to full checks. For example, in the first part to select number=5 или number=6 you cannot write (name_var,value) in (('number','5'),('number','6'),('abcd','xyz')) because in this case, count(1) will give an incorrect result. In this case, you need to write something like ( (name_var='number' and (value='5' or value='6') ) OR (name_var='abcd' and 'value'='xyz') )

        On the contrary, in a subquery (which in not exists) you can list any invalid values, as shown in the main example.