Post reply

T-SQL Minus equivalent

  • June 24, 2006 at 3:21 am

    #91560

    Hi,

    I have a table with 5 columns.  2 of these columns are ActionID and CallNo.

     ActionID CallNo
     5 1411
     5 1412
     6 1412
     5 1413
     5 1414
     6 1414
     5 1415

    What I am trying to do is retrieve all the rows where the ActionID has been set to 5 and where there is no ActionID of 6 for a specific CallNo.  For example, n the table above I would like to be able to extract the rows that contain CallNo's 1411, 1413 and 1415.  I could do this with Oracle using the Minus command but am struggling to do it with T-SQL - which I'm fairly new to. 

    Any ideas?

    Thanks.

     

  • June 24, 2006 at 5:53 am

    #645716

    If you are using SQL Server 2005 MINUS in Oracle is equivalent to EXCEPT in SQL Server 2005 this is not available in SQL Server 2000.

    Anyway I wouldn't use the set difference operators to do the above I would do something like this probably

    SELECT ActionID, CallNo

    FROM Table

    WHERE ActionID = 5 and CallNo NOT IN (SELECT CallNo FROM Table Where ActionID = 6)

    hth

    David

     

  • June 24, 2006 at 6:12 am

    #645717

    Hi David,

    I'm using SQL2000 and that worked a treat.

    Thanks

    Terry

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply