A Complicated Join - 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

SQL Server 2000 Forums

Transact-SQL (2000)

A Complicated Join

Author

Topic

golden_manish
Starting Member

13 Posts

Posted - 2004-04-24 : 08:56:14

Hello Friends,

This Is Manish, From India.
I have a small and tricky little problem, having a hard time finding a solution.
Hope you guys help me.

Problem:
I have a Purchase Table (Master As Well As Detail Table)
I have to find out The Last Purchased Items from a single Supplier (Date Wise)

Purchase Master
Purchase Entry Id (Primary Key)
Purchase Entry Date
Supplier …

Purchase Detail
Purchase Entry Id (Primary Key)
Purchase Entry Sr. (Primary Key)
Item Id
Item Rate …

Considerations:

Here is the tricky part. The user can make any entry of any date he wants (Back Dated, Front Dated Entry), therefore the Purchase Entry Id (The Primery Key) cannot be used to retrieve data simultaneously from the master as well as detail table using a simple join.

I have to first find the Last Purchase Entry Date from the master and then join it to the detail to find out detail Item records last sold my the supplier.
Its not working!

The result expected is some thing like this:

The Fields are listed bellow:
Purchase Entry Id (Ex. 1, 3, 5)
Purchase Entry Date (Ex. 10/10/2004, 11/11/2004, 12/12/2004)
Purchase Entry Sr. (Ex, 1, 6, 9)
Item Id (Ex. 1, 10, 500)
Item Rate … (Ex. 100, 200, 300)

Please Help

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-25 : 00:34:48

So, are you looking for something like this????

SELECT
pm.Supplier,
pm.PurchaseEntryId,
pm.PurchaseEntryDate,
pd.ItemId,
pd.ItemRate
FROM
PurchaseDetail pd
INNER JOIN PurchaseMaster pm ON pd.PurchaseEntryId = pm.PurchaseEntryId
INNER JOIN (
SELECT
MAX(PurchaseEntryDate) AS PurchaseEntryDate,
Supplier
FROM
PurchaseMaster pm
WHERE
Supplier = @Supplier) pm2 ON pm.PurchaseEntryDate = pm2.PurchaseEntryDate
AND pm.Supplier = pm2.Supplier

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources