Query Relation Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Relation Help

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-07-29 : 12:55:54

Hi I have the following Query:

SELECT INVENTTABLE.ITEMID, INVENTTABLE.ITEMNAME, INVENTTRANS.DATEPHYSICAL,
ISNULL((INVENTSUM.POSTEDQTY + INVENTSUM.RECEIVED - INVENTSUM.DEDUCTED + INVENTSUM.REGISTERED - INVENTSUM.PICKED), 0) AS ONHAND

FROM INVENTTABLE

INNER JOIN INVENTTRANS ON INVENTTABLE.DATAAREAID = INVENTTRANS.DATAAREAID AND INVENTTABLE.ITEMID = INVENTTRANS.ITEMID

INNER JOIN INVENTSUM ON INVENTTABLE.ITEMID = INVENTSUM.ITEMID AND INVENTTABLE.DATAAREAID = INVENTSUM.DATAAREAID

WHERE INVENTTABLE.DATAAREAID = 'MEX' AND INVENTTRANS.TRANSTYPE = '0'

Some data from the 3 tables:

INVENTTABLE
ItemID
00001
00002

INVENTTRANS
ItemID DatePhysical
00001 2009-5-14
00001 2008-5-15
00001 2009-5-13
00002 2009-8-08
00002 2009-9-08

INVENTSUM
ItemID PostedQty
00001 500
00001 0
00001 600
00002 200
00002 300

I want my select statement to come back with 1-1 relations. Currently inner joining this data gives multiple records for the 1:N relation.

I need to grab the PHYSICALDATE closests todays date but I'm not quite sure how to accomplish this.

Currently the query outputs:

00001 name1 2009-5-14 500
00001 name1 2008-5-15 0
00001 name1 2009-5-13 600
00002 name2 2009-8-18 200
00002 name2 2009-9-08 300

I need the query to grab the date closest to today and 1-1:

00001 name1 2009-5-14 500
00002 name2 2009-9-08 300

Any ideas? Thanks in advance for your help.

ceerock
Starting Member

1 Post

Posted - 2009-07-29 : 16:24:15

SELECT INVENTTABLE.ITEMID, INVENTTABLE.ITEMNAME, INVENTTRANS.DATEPHYSICAL,
ISNULL((INVENTSUM.POSTEDQTY + INVENTSUM.RECEIVED - INVENTSUM.DEDUCTED + INVENTSUM.REGISTERED - INVENTSUM.PICKED), 0) AS ONHAND

FROM INVENTTABLE

INNER JOIN INVENTTRANS ON INVENTTABLE.DATAAREAID = INVENTTRANS.DATAAREAID AND INVENTTABLE.ITEMID = INVENTTRANS.ITEMID

INNER JOIN INVENTSUM ON INVENTTABLE.ITEMID = INVENTSUM.ITEMID AND INVENTTABLE.DATAAREAID = INVENTSUM.DATAAREAID
INNER JOIN (--Creates a one to one between item id and date
SELECT INVENTTABLE.ITEMID, MAX(INVENTTRANS.DATEPHYSICAL AS MAXDATE
FROM INVENTTABLE INNER JOIN
INVENTTRANS ON INVENTTRANS.ITEMID = INVENTTABLE.ITEMID
GROUP BY INVENTTABLE.ITEMID) AS MAXID ON INVENTTABLE.ITEMID = MAXID.ITEMID AND INVENTTRANS.DATEPHYSICAL = MAXID.MAXDATE

WHERE INVENTTABLE.DATAAREAID = 'MEX' AND INVENTTRANS.TRANSTYPE = '0'

Hope this helps...Does the INVENTTRANS and INVENTSUM TABLES have Primary Key's?

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-07-29 : 16:55:51

Thanks, ceerock, I am going to test this tomorrow and I'll let you know. The primary keys for both of those tables are itemid along with a few other fields.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:00:25

dont you have a unique valued column in INVENTSUM table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources