SQLTeam.com | Weblogs | Forums

Same id different value


#1

I know this is very simple but for the life of me i cannot fix it.
I'm trying to find values with same trans_lgnnumber and different barcode.

select a.transt_lgnnumber,
       b.transt_lgnnumber
  from tbltrans_ticket a
  join tbltrans_ticket b on b.transt_lgnnumber = a.transt_lgnnumber
where a.transt_lgnnumber <> b.transt_lgnnumber
and a.TransT_strBarcodeRedemp = b.TransT_strBarcodeRedemp
  and a.TransT_dtmDateTime > '20170101'
  and (a.TransT_strBarcodeRedemp is not null or a.TransT_strBarcodeRedemp <> '')

#2

sample data would help. how about.

select a.transt_lgnnumber,
       b.transt_lgnnumber
  from tbltrans_ticket a
  join tbltrans_ticket b on b.transt_lgnnumber = a.transt_lgnnumber
where a.transt_lgnnumber <> b.transt_lgnnumber
and (a.TransT_strBarcodeRedemp = b.TransT_strBarcodeRedemp
    OR (a.TransT_strBarcodeRedemp IS NULL AND b.TransT_strBarcodeRedemp IS NOT NULL)
    OR (a.TransT_strBarcodeRedemp IS NOT NULL AND b.TransT_strBarcodeRedemp IS NULL))
  and a.TransT_dtmDateTime > '20170101';

#3

does not work, brings empty.
so

select transt_lgnnumber,transT_strBarcodeRedemp from tbltrans_ticket
  where TransT_dtmDateTime > '20170101'
  order by transt_lgnnumber desc

transt_lgnnumber, transT_strBarcodeRedemp
120092, 9XX40000000000000007
120091, 9XX40000000000000007

is there a chance that the equation does not work because the barcode is varchar(30) and not an integer?

Thanks


#4

I guess I made a mistake

  1. a.transt_lgnnumber <> b.transt_lgnnumber should be a.transt_lgnnumber = b.transt_lgnnumber (same number)
  2. a.TransT_strBarcodeRedemp = b.TransT_strBarcodeRedemp should be a.TransT_strBarcodeRedemp <> b.TransT_strBarcodeRedemp (different numbers)

sample data and expected output would be nice


#5

The issue here is that I am getting transaction numbers that may have 2 same transaction numbers so they are duplicated, sorry I missed that.
So i can get this that is duplicate:
120091, 9XX40000000000000007
120091, 9XX40000000000000007

Can i remove those transactions?


select a.transt_lgnnumber,
       b.transt_lgnnumber
  from tbltrans_ticket a
  join tbltrans_ticket b on b.transt_lgnnumber = a.transt_lgnnumber
where a.transt_lgnnumber = b.transt_lgnnumber
and (a.TransT_strBarcodeRedemp <> b.TransT_strBarcodeRedemp )
    OR (a.TransT_strBarcodeRedemp IS NULL AND b.TransT_strBarcodeRedemp IS NOT NULL)
    OR (a.TransT_strBarcodeRedemp IS NOT NULL AND b.TransT_strBarcodeRedemp IS NULL)
  and a.TransT_dtmDateTime > '20170101'
  group by a.transt_lgnnumber,
       b.transt_lgnnumber
  order by a.transt_lgnnumber
  ;

thanks

Have in mind that transt_lgnnumber can appear more than once in the same number as it is multiple transactions with the same number


#6

Your code is missing a set of parenthesis around the or section.

where a.transt_lgnnumber = b.transt_lgnnumber
and (    (a.TransT_strBarcodeRedemp <> b.TransT_strBarcodeRedemp )
    OR (a.TransT_strBarcodeRedemp IS NULL AND b.TransT_strBarcodeRedemp IS NOT NULL)
    OR (a.TransT_strBarcodeRedemp IS NOT NULL AND b.TransT_strBarcodeRedemp IS NULL)   )
  and a.TransT_dtmDateTime > '20170101'

#7

Hi.
I do not get the correct results:

select a.transt_lgnnumber,
       b.transt_lgnnumber,
	   a.TransT_strBarcodeRedemp
  from tbltrans_ticket a
  join tbltrans_ticket b on b.transt_lgnnumber = a.transt_lgnnumber
where a.transt_lgnnumber = b.transt_lgnnumber
and (    (a.TransT_strBarcodeRedemp <> b.TransT_strBarcodeRedemp )
    OR (a.TransT_strBarcodeRedemp IS NULL AND b.TransT_strBarcodeRedemp IS NOT NULL)
    OR (a.TransT_strBarcodeRedemp IS NOT NULL AND b.TransT_strBarcodeRedemp IS NULL)   )
  and a.TransT_dtmDateTime > '20170101';

When i then do select * from tbltrans_ticket where TransT_strBarcodeRedemp = 'THEBARCODES' i get barcodes that have been used in a transaction that have 2 same transaction number. As described above.


#8

OK.
So As I couldn't find a solution I went medieval on this one

select transt_lgnnumber,TransT_strBarcodeRedemp
  into #gt
  from tbltrans_ticket where TransT_strBarcodeRedemp is not null
  and TransT_strBarcodeRedemp <> ''
  -- and TransT_dtmDateTime > '20170101'
   group by transt_lgnnumber,TransT_strBarcodeRedemp 

  
   SELECT G.TransT_strBarcodeRedemp 
   into #gtN
   FROM #gt G
    GROUP BY G.TransT_strBarcodeRedemp
  HAVING ( COUNT(G.transt_lgnnumber) > 1 )

  select T.transt_lgnnumber,G.TransT_strBarcodeRedemp from #gtn G
  inner join tbltrans_ticket T on G.TransT_strBarcodeRedemp = T.TransT_strBarcodeRedemp
  order by T.transt_lgnnumber


  drop table #gt
  drop table #gtn