Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    12

    Unanswered: 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 advance

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Welcome to the Forum.
    Check this...
    Code:
    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
    Last edited by rudra; 09-15-06 at 08:33.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2006
    Posts
    12
    Hi Rudra !

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

  5. #5
    Join Date
    Sep 2006
    Posts
    12
    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 ?

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by psutradhar
    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...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by r937
    Code:
    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...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Sep 2006
    Posts
    12
    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 ?

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by psutradhar
    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...

    Code:
    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.
    Last edited by rudra; 09-15-06 at 09:01.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •