Clever Geek Handbook

How to get records of fields by comparing other mssql fields?

I have a table; let it be called table1; with the following fields and data

msgid  msisdn teaserid send
1      333     1        1
2      333     1        0
3      444     2        1
4      444     2        1
5      444     3        1

I need a query that returns those messages for which send = 1 for every record that has the same msisdn, teaserid. In the above case, I want msgid: 3,4,5. How can this be done with an mssql query?

+3


source to share


2 answers


This is a nice use of window functions :

declare @t table (msgid int,msisdn int,teaserid int,send int)
insert into @t (msgid,msisdn,teaserid,send) values
(1,333,1,1),
(2,333,1,0),
(3,444,2,1),
(4,444,2,1),
(5,444,3,1)

select * from (
select *,MIN(send) OVER (PARTITION BY msisdn,teaserid) as all1
from @t
)t
where all1 = 1

Result:



msgid       msisdn      teaserid    send        all1
----------- ----------- ----------- ----------- -----------
3           444         2           1           1
4           444         2           1           1
5           444         3           1           1

When calculating MIN(send)

in sections msisdn,teaserid

, it can only be 1 if all values send

are 1. If only one row has 0, this will be the minimum for that section.

+2


source


You can use this query to get the result



 select msgid 
 from table1 t 
 where send=1 
       and exists(select * from table
                  where send=1 
                  and msisdn=t.msisdn
                  and teaserid=t.teaserid and msgid != t.msgid)
+1


source






More articles:


All Articles