Tables relationals problem getting duplicates

[evr72]

Hello,

I have two tables orders and shopord

In my orders table I have the following fields

Item
Order
Qty
Date

In my shpord table I have the following fields

Item
OrderNM
QtyNM
DateNM

The problem is that both table have the same item numbers but not the same dates, not the same Orders and not the same qty

so if I query one to the other I get someting like this

Item   Order   OrderNM      Qty    QtyNM       Date    DateNM
0011   1234    ABC123       500      800     1/15/14    1/25/14

But if I have more than one Order (using orders as primary table) then I get something like this

Item   Order   OrderNM      Qty    QtyNM       Date    DateNM
0011   1234    ABC123       500      800     1/15/14    1/25/14
0011   1235    ABC123       200      700     1/31/14    2/03/14
0011   1236    ABC123       900      200     2/14/14    2/22/14
0011   1234    ABC123       500      800     1/15/14    1/25/14
0011   1235    ABC123       200      700     1/31/14    2/03/14
0011   1236    ABC123       900      200     2/14/14    2/22/14
0011   1234    ABC123       500      800     1/15/14    1/25/14
0011   1235    ABC123       200      700     1/31/14    2/03/14
0011   1236    ABC123       900      200     2/14/14    2/22/14

I tried to group them but that did not work well would there be a way to get just this

0011   1234    ABC123       500      800     1/15/14    1/25/14
0011   1235    ABC123       200      700     1/31/14    2/03/14
0011   1236    ABC123       900      200     2/14/14    2/22/14

I appreciate any help!!

Thanks!!!

 

[Andrzejek]

Could you show the SELECT statement you have?

Have fun.

---- Andy

 

[evr72]

Sorry, yes I forgot to post it. I have been at this all day

SELECT OPENORD1.ITEM, OPENORD1.ORDER, MOMASTFL.ORDNO AS ORDERNM, OPENORD1.QTY, MOMASTFL.ORQTY AS QTYNM, OPENORD1.CJAKDT AS DT, MOMASTFL.ODUDT AS DTNM
FROM OPENORD1 LEFT JOIN MOMASTFL ON OPENORD1.ITEM = MOMASTFL.FITEM;

here is the actual result

ITEM	ORDER	ORDERNM	QTY	QTYNM	DT	DTNM
0066	 571673	M461570	1200	2500	1140508	1140509
0066	 571673	M461550	1200	2500	1140508	1140131
0066	 571673	M461560	1200	2500	1140508	1140307
0066	 571671	M461570	1200	2500	1140306	1140509
0066	 571671	M461550	1200	2500	1140306	1140131
0066	 571671	M461560	1200	2500	1140306	1140307
0066	 571672	M461570	1200	2500	1140408	1140509
0066	 571672	M461550	1200	2500	1140408	1140131
0066	 571672	M461560	1200	2500	1140408	1140307
0066	 568515	M461570	547	2500	1140217	1140509
0066	 568515	M461550	547	2500	1140217	1140131
0066	 568515	M461560	547	2500	1140217	1140307
0066	 571675	M461570	1200	2500	1140708	1140509
0066	 571675	M461550	1200	2500	1140708	1140131
0066	 571675	M461560	1200	2500	1140708	1140307
0066	 571674	M461570	1200	2500	1140605	1140509
0066	 571674	M461550	1200	2500	1140605	1140131
0066	 571674	M461560	1200	2500	1140605	1140307
0066	 571670	M461570	1200	2500	1140226	1140509
0066	 571670	M461550	1200	2500	1140226	1140131
0066	 571670	M461560	1200	2500	1140226	1140307

 

[Andrzejek]

Could you show what you get when you do:[tt]

SELECT OPENORD1.*
FROM OPENORD1
WHERE ITEM = '0066'
[/tt]
And do the same for MOMASTFL table

(I think they are pretty small)

Have fun.

---- Andy

 

[evr72]

OPENORD1 table

ITEM	ORDER	QTY	DT
0066	 568515	547	1140217
0066	 571670	1200	1140226
0066	 571671	1200	1140306
0066	 571672	1200	1140408
0066	 571673	1200	1140508
0066	 571674	1200	1140605
0066	 571675	1200	1140708

MOMASTFL table

ORDERNM	FITEM	OTYNM	DTNM
M461550	0066	2500	1140131
M461560	0066	2500	1140307
M461570	0066	2500	1140509

the key records are the ordernm and order

 

[Andrzejek]

So this part from your original post must be related to somethig else:

[pre]
0011 1234 ABC123 500 800 1/15/14 1/25/14
0011 1235 ABC123 200 700 1/31/14 2/03/14
0011 1236 ABC123 900 200 2/14/14 2/22/14
[/pre]
because if you only go after OPENORD1 table where ITEM = '0066' you get the 7 records presented (not 3)

Have fun.

---- Andy

 

[MajP]

Your results are fine, there are no duplicates as you describe. In your example you showed duplicate records, in your real data there are no duplicates. Every combination of Order and OrderNM is unique. What results did you expect?

 

[evr72]

Andrzejek, I am not sure what you mean, it is the same type of data just not the same numbers.

 

[Andrzejek]

Like MajP stated, if you have 7 unique records in OPENORD1 table and 3 unique records in MOMASTFL table and join them in ITEM fields, for every record in OPENORD1 table you get 3 records from MOMASTFL table, which makes 21 records total. And that's what you showed.

"What results did you expect?" based on the data sample showed in your post from 21 Jan 14 16:05

Do you want ALL records from MOMASTFL table and some (which ones?) records from OPENORD1 table?

Have fun.

---- Andy

 

[MajP]

In your notional data you show duplicates such as:

Item   Order   OrderNM      Qty    QtyNM       Date    DateNM
0011   1234    ABC123       500      800     1/15/14    1/25/14
...
0011   1234    ABC123       500      800     1/15/14    1/25/14

But in your real data there are no duplicates. So what rows in your real data do you not want to see? Hard to recommend a solution without seeing a problem.