locked
Out of Range Values RRS feed

  • Question

  • CREATE TABLE #table1 (id INT , serial INT)

    INSERT  INTO #table1 VALUES  ( 1 , 1 )

    INSERT  INTO #table1 VALUES  ( 1 , 2 )

    INSERT  INTO #table1 VALUES  ( 1 , 3 )

    INSERT  INTO #table1 VALUES  ( 1 , 4 )

    INSERT  INTO #table1 VALUES  ( 1 , 5 )

    INSERT  INTO #table1 VALUES  ( 2 , 1 )

    INSERT  INTO #table1 VALUES  ( 2 , 2 )

    INSERT  INTO #table1 VALUES  ( 2 , 3 )

    INSERT  INTO #table1 VALUES  ( 2 , 4 )

    INSERT  INTO #table1 VALUES  ( 2 , 5 )

     

    CREATE TABLE #table2 (id INT , startnbr INT , ENDnbr INT)

    INSERT  INTO #table1 VALUES  ( 1 , 1 , 4 )

    INSERT  INTO #table1 VALUES  ( 1 , 1 , 3 )

     

     

    I want to find the serial in #table1 from #table2 which are not between #table2 startnbr and enbnbr against each id.

    As the result should be like

    ID            Serial

    1              5

    2              4

    2              5


    Regards, Muhammad Bilal.
    Thursday, November 25, 2010 8:35 AM

Answers

  • select t1.* from #table1 t1
    left outer join #table2 t2 on t1.Id = t2.Id and t1.serial between t1.startnbr and t2.endnbr
    where t2.Id is null
    
    
    • Proposed as answer by Rishabh K Thursday, November 25, 2010 9:41 AM
    • Marked as answer by Muhammad Bilal Thursday, November 25, 2010 11:02 AM
    Thursday, November 25, 2010 8:53 AM
  • /*CREATE TABLE #table1 (id INT , serial INT)
    INSERT INTO #table1 VALUES ( 1 , 1 )
    INSERT INTO #table1 VALUES ( 1 , 2 )
    INSERT INTO #table1 VALUES ( 1 , 3 )
    INSERT INTO #table1 VALUES ( 1 , 4 )
    INSERT INTO #table1 VALUES ( 1 , 5 )
    INSERT INTO #table1 VALUES ( 2 , 1 )
    INSERT INTO #table1 VALUES ( 2 , 2 )
    INSERT INTO #table1 VALUES ( 2 , 3 )
    INSERT INTO #table1 VALUES ( 2 , 4 )
    INSERT INTO #table1 VALUES ( 2 , 5 )
     
    CREATE TABLE #table2 (id INT , startnbr INT , ENDnbr INT)
    INSERT INTO #table2 VALUES ( 1 , 1 , 4 )
    INSERT INTO #table2 VALUES ( 2 , 1 , 3 )
    
    */
    
    --select * from #table1
    --select * from #table2
    --truncate table #table2
    
    select 
    a.id
    ,a.Serial
    --,* 
    from #table1 a
    	 inner join #table2 b 
    		On a.id = b.id
    	where a.serial < b.startnbr
    			or a.serial > b.Endnbr
    

    HI check the above code 

    i guess for #table2 u have given wrong insert values .. i modified based on ur result expected :) 


    Hope that helps ... Kunal
    • Marked as answer by Muhammad Bilal Thursday, November 25, 2010 11:02 AM
    Thursday, November 25, 2010 8:58 AM

All replies

  • select t1.* from #table1 t1
    left outer join #table2 t2 on t1.Id = t2.Id and t1.serial between t1.startnbr and t2.endnbr
    where t2.Id is null
    
    
    • Proposed as answer by Rishabh K Thursday, November 25, 2010 9:41 AM
    • Marked as answer by Muhammad Bilal Thursday, November 25, 2010 11:02 AM
    Thursday, November 25, 2010 8:53 AM
  • /*CREATE TABLE #table1 (id INT , serial INT)
    INSERT INTO #table1 VALUES ( 1 , 1 )
    INSERT INTO #table1 VALUES ( 1 , 2 )
    INSERT INTO #table1 VALUES ( 1 , 3 )
    INSERT INTO #table1 VALUES ( 1 , 4 )
    INSERT INTO #table1 VALUES ( 1 , 5 )
    INSERT INTO #table1 VALUES ( 2 , 1 )
    INSERT INTO #table1 VALUES ( 2 , 2 )
    INSERT INTO #table1 VALUES ( 2 , 3 )
    INSERT INTO #table1 VALUES ( 2 , 4 )
    INSERT INTO #table1 VALUES ( 2 , 5 )
     
    CREATE TABLE #table2 (id INT , startnbr INT , ENDnbr INT)
    INSERT INTO #table2 VALUES ( 1 , 1 , 4 )
    INSERT INTO #table2 VALUES ( 2 , 1 , 3 )
    
    */
    
    --select * from #table1
    --select * from #table2
    --truncate table #table2
    
    select 
    a.id
    ,a.Serial
    --,* 
    from #table1 a
    	 inner join #table2 b 
    		On a.id = b.id
    	where a.serial < b.startnbr
    			or a.serial > b.Endnbr
    

    HI check the above code 

    i guess for #table2 u have given wrong insert values .. i modified based on ur result expected :) 


    Hope that helps ... Kunal
    • Marked as answer by Muhammad Bilal Thursday, November 25, 2010 11:02 AM
    Thursday, November 25, 2010 8:58 AM