select barcode, fullname, social, printdate, (CASE WHEN min(orderId) = 0 THEN 'yes' WHEN min(orderid) <> 0 then 'No' END) as Reprint from clientdata (nolock) left outer join ReprintTable WITH(nolock) on Code = barcode where clientcode = '334556' --and printdate < '2021-02-23' group by barcode, fullname, social, printdate order by printdate
Logic behind this query:
So basically I want to show all the reprinted card but also the non-reprinted one, I'm using left outer join to join reprint table (which stores all the information for reprint cards, like reprint date)
Basically if the orderid of card is 0, then that means the cards has been reprinted, vice versa.
I want to get my query to show all the non-reprinted cards and excluded the reprinted cards that was reprinted before 23rd, but once I added that and clause in, the non-reprinted cards are no longer showing.
How do I fix this.