A SINGLE QUERY FROM TWO TABLES

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

SQL Server Development (2000)

A SINGLE QUERY FROM TWO TABLES

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-19 : 09:03:57

pradeep writes "i have three tables as under


 Table(1)
 Name       :Stores_Item
 Columns    :Item_id(PK)
             Item_name
             Quantity_available
             Reorder_level 

 Table(2)
 Name       :Stores_Purchase
 Columns    :Item_id(FK) --refers Stores_item.Item_id
             Date_of_purchase
             Quantity_purchased

 Table(3)
 Name       :Stores_Issue
 Columns    :Item_id  --refers Stores_item.Item_id
             Date_of_Issue
             Quantity_issued

I need a single query to fetch the sum of quantity_purchased and sum of Quantity_issued for a date period say one month,which is a variable
..But the result set should have ALL the Item_id's which are in the Table Stores_item
even if there are some Item_ids for which there are no details they should be a nulll in the result set..

Can anyone out there pls help me..
I really thank u all in advance

PRADEEP"

Nazim
A custom title

1408 Posts

Posted - 2001-12-19 : 09:12:30

select *,(select sum(quantity_purchased) from stores_purchase
where date_of_purchase between @startdate and @enddate) as Purchase, (select sum(quantity_issued) from stores_issues
where date_of_issued between @startdate and @enddate) as Sales
from stores_item

-------------------------
"Success is when Preparedness meets Opportunity"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources