Sql-server – Select rows from table having specific values depending on another values in the same table

database-designMySQLquerysql serversubquery

I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example:

Userpin  TrDate       Time1      Tr_type
4555     12-02-2020    7:30         7
4555     12-02-2020    14:30        8
6985     12-02-2020    7:34         7
9854     12-02-2020    7:15         7
8542     12-02-2020    7:16         7
8542     12-02-2020    14:40        8

Using this data set I am looking for Query To Get
that data with userpin that have only tr_type equal 7 in current day
and i want the result like that : 

Userpin  TrDate       Time1      Tr_type
6985     12-02-2020    7:34         7
9854     12-02-2020    7:15         7

i used that query to get data in current day

select UserPIN ,TrDate,Time1,Tr_Type
from mytable
where UserPIN in (6985,9854,4555,8542,4062,7070,4189,4197)
and TrDate=CAST(GETDATE() AS DATE)

This seems like a simple thing to query, but I am finding myself up against a wall. What's the best way to accomplish this in a reasonably efficient way?

Best Answer

SELECT t1.*
FROM mytable t1
WHERE t1.Tr_type = 7
  /* AND another conditions by Userpin list, TrDate, etc. */
  AND NOT EXISTS ( SELECT NULL
                   FROM mytable t2
                   WHERE t1.Userpin = t2.Userpin
                     /* AND another conditions */
                     AND t2.Tr_type = 8 )
Related Question