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, 2020 at 1:04

1 Answer 1

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, 2020 at 6:50
  • @ahmed I don't know HOW you checked, but you clearly did it wrong. fiddle.
    – Akina
    Feb 19, 2020 at 6:56
  • now it work after i added my conditions in the two select statement , thanks a lot
    – ahmed
    Feb 19, 2020 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.