item master and office stock details (was "Query Problem")

Hi,

I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:

ITEM MASTER
-----
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E

STOCKDETAILS
-----------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3

I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1

--------------
FOR OFFICE - 1
--------------
ITEMCODE ITEMNAME OFFICEID
--------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL

i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.

Thanks in advanceWelcome to the Forum.
Check this...

SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = '1') AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1|||Hi Rudra !

how to get such all result in view and then i will query for officeid to get my desired output.|||Hi r937 !

I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?|||Hi r937 !

I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?
Why don't you use stored proc and pass parameter using OFFICEID ? I think that would be a better way to deal with your problem.But I am not sure what your requirement is...|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1

hmm,always ahead...;)|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?

if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?

my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?

if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?

my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?

I suggest you to use stored proc,its always good to use stored proc
in your case.Just write this...

CREATE PROCEDURE dbo.StockView(
@.officeid VARCHAR(20)

AS

--Use mine or Rudy's one
--this is mine
SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = @.officeid) AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME

--OR use Rudy's one

select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = @.officeid

Go

And check BOL to use Stored proc in dataset .Its very easy man and better to use in many respect
Hope this will help you.sql