0

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?

1
  • Which is it? MySQL or Sql-Server?
    – Rick James
    Feb 24 '20 at 1:04
0
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 )
3
  • i test it but still not return any values !!
    – ahmed
    Feb 19 '20 at 6:50
  • @ahmed I don't know HOW you checked, but you clearly did it wrong. fiddle.
    – Akina
    Feb 19 '20 at 6:56
  • now it work after i added my conditions in the two select statement , thanks a lot
    – ahmed
    Feb 19 '20 at 6:59

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .