Skip to Content
0
Former Member
May 08, 2008 at 04:47 PM

Join of OIBT and IBT1 doubles the data in Query Generator

1492 Views

I have been struggling with Batch reporting for a while now. The problem I am having is when joining OIBT and IBT1, I get a doubling of data on my reports.

OIBT T0 INNER JOIN IBT1 T1 ON T0.ItemCode = T1.ItemCode AND T0.BatchNum = T1.BatchNum

The entire query looks like this:

SELECT T1.[ItemCode], T1.[BatchNum], T1.[BsDocEntry] FROM OIBT T0 INNER JOIN IBT1 T1 ON T0.ItemCode = T1.ItemCode AND T0.BatchNum = T1.BatchNum WHERE T0.[BatchNum] = 'AA1234' ORDER BY T1.[BatchNum], T1.[BsDocEntry]

Item Code Batch Doc Key

1000 AA1234 29

1000 AA1234 29

1000 AA1234 33

1000 AA1234 33

...

When I do the same report from the IBT1 table without the join, I get

Item Code Batch Doc Key

1000 AA1234 29

1000 AA1234 33

...

I believe the problem is in my join. Any SQL experts have any suggestions?

Thanks

Mike