Join question...

Question

Post reply

Join question...

David-367465

Valued Member

Points: 72
More actions
September 15, 2006 at 9:33 am

#170361

Fairly new at this and I'm lost at the moment.
I have the following code:
SELECT
IV00101.ITEMNMBR AS Expr1, IV00101.ITEMDESC, IV00101.CURRCOST, IV00101.ITEMSHWT, IV00101.USCATVLS_1, IV00101.USCATVLS_2,
IV00101
.USCATVLS_3, IV00102.QTYONHND, IV00107.UOFM
FROM
IV00102 INNER JOIN
IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
IV00107
ON IV00102.ITEMNMBR = IV00107.ITEMNMBR
WHERE
(IV00107.PRCLEVEL = 'ZWHOLESALE')

It pulls the data needed form the 3 tables, but it is adding duplicate records to the resultset (2 of each item). How do I pull my data from the 3 tables into one table and eliminate the duplication at the same time?

Thanks in advance!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

David,

Great plains gets u doesn't it? It has been almost 2 years since I've worked with GP. But my understanding is this:

IV00101 is Inventory Master

IV00102 is Inventory Location (Here u will have the same Item Number-ITEMNMBR for every location that you have)

IV00107 is Inventory Pricing (Here u will have the same Item Number-ITEMNMBR for every Price Group that you have)

Now that you understand the DB you know why u arer getting dupe data(1 item -> Many Loaction)

Hope this helps.

Thanks

Sreejith

David-367465 Valued Member Points: 72 More actions · Answer 2

When I first started reading your post, I had to look around for hidden cameras

You hit the nail on the head! I didn't realize there was a "Master" record and one or more "Site" records for each item in IV00102. I was able to add another value to the where clause to only select the "Master" records thus eliminating the duplicates.

Thank you very much,

David

Just in case it might help anyone else, here is the revised code:

SELECT

IV00101.ITEMDESC, IV00101.ITEMNMBR, IV00107.UOFM, IV00101.CURRCOST, IV00101.ITEMSHWT, IV00101.USCATVLS_1, IV00101.USCATVLS_2,

IV00101.USCATVLS_3, IV00102.QTYONHND

FROM

IV00102 INNER JOIN

IV00101 INNER JOIN

IV00107 ON IV00101.ITEMNMBR = IV00107.ITEMNMBR ON IV00102.ITEMNMBR = IV00107.ITEMNMBR

WHERE

(IV00107.PRCLEVEL = 'ZWholesale') AND (IV00102.RCRDTYPE = 1)

ORDER

BY IV00101.ITEMNMBR