Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: How to get unmatched rows in the query

    The following stored procedure is returning the correct row(s). But I am not sure it is correct.
    In the stored procedure I need the variable passed in (@lotnum) in the dbo.tblBag_data compare with dbo.tblBag_results and return unmatch rows from dbo.tblBag_data. Basically I need the unmatched records in dbo.tblBag_data between two tables. For example there are five records. In dbo.tblBag_data 1st record contains lotnum(1000), bag1. next record contains lotnum(1000), bag2. .. last record contains lotnum(1000), bag5. In dbo.tblBag_results there are two bags. I need the three records that do not match up. Thanks!


    CREATE PROCEDURE sp1
    AS

    declare @lotnum int

    set @lotnum = 4056;
    SELECT dbo.tblBag_data.work_ord_num, dbo.tblBag_data.work_ord_line_num, dbo.tblBag_data.bag_num, dbo.tblBag_data.lotnum, dbo.tblBag_data.bag_status
    FROM dbo.tblBag_data
    WHERE dbo.tblBag_data.lotnum = @lotnum AND dbo.tblBag_data.bag_num not in (select dbo.tblBag_results.bag_num from dbo.tblBag_results Where dbo.tblBag_results.lotnum = @lotnum)
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If I understand you requirements correctly, your procedure should work correctly.

    Using a LEFT OUTER JOIN is generally preferable to using NOT IN, though:

    SELECT dbo.tblBag_data.work_ord_num, dbo.tblBag_data.work_ord_line_num, dbo.tblBag_data.bag_num, dbo.tblBag_data.lotnum, dbo.tblBag_data.bag_status
    FROM dbo.tblBag_data
    left outer join dbo.tblBag_results
    on @lotnum dbo.tblBag_data.bag_num = dbo.tblBag_results.bag_num
    and dbo.tblBag_results.lotnum = @lotnum
    where dbo.tblBag_results.bag_num is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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