none
how to avoid or replace a sub query from Exists statement.

    Question

  • Hello all,

    I have a query regarding sql query.

        

    DB 1
    -----

    Table A                                 Table B
    --------
    ID   Code   HeaderID            ID   
    --     ---       --------                 ---
    1    100      1                          1
    2    200      1                          2
    3    100      2                          3

    DB 2
    ----

    Table C
    -------
    ID    Code  Type
    --------------------
    1     100     1
    2     101     1
    3     100     2

    set @HeaderID = 1
    IF EXISTS (SELECT Code From Table A WHERE HeaderID=@ID AND Code  not in (SELECT Code FROM Table C WHERE Type=1))
    BEGIN
    RAISERROR('code is not set.',16,1)
    END

    Table A and Table C are in different databases say DB1 and DB2.

    How can we replace this with another query using join or something. Can we do it using one query by avoiding sub query.I want to get Code 200 which is not match with Table C Code.


    Regards, Ranjith T Rajan Acty System India Pvt Ltd.


    Saturday, March 15, 2014 12:01 PM

Answers

  • Try the below:

    Select A.* From TableA A
    Left Join TableC C On A.Code=C.Code and A.ID=C.ID
    Where A.Code is null



    Saturday, March 15, 2014 12:06 PM
  • You can use Not exists or left join:

    declare @HeaderID int
    set @HeaderID = 1
    
    SELECT Code From DB2..tablea A WHERE HeaderID=@HeaderID 
    and not exists (SELECT Code FROM DB1..tablec C WHERE Type=1 and code=a.code)
    
    Select A.code From DB2.dbo.tablea A
    Left Join DB1..tablec C On A.Code=C.Code and A.ID=C.ID and c.Type=1
    WHERE HeaderID=@HeaderID  
    and c.Code is null
    
    
    
    SELECT Code From DB2..tablea A WHERE HeaderID=@HeaderID AND Code  not in (SELECT Code FROM DB1..tablec C WHERE Type=1)
    
    
    SELECT Code From DB2..tablea A WHERE HeaderID=@HeaderID 
    except
    SELECT Code FROM DB1..tablec C WHERE Type=1

    Saturday, March 15, 2014 2:02 PM
    Moderator

All replies

  • Try the below:

    Select A.* From TableA A
    Left Join TableC C On A.Code=C.Code and A.ID=C.ID
    Where A.Code is null



    Saturday, March 15, 2014 12:06 PM
  • You can use Not exists or left join:

    declare @HeaderID int
    set @HeaderID = 1
    
    SELECT Code From DB2..tablea A WHERE HeaderID=@HeaderID 
    and not exists (SELECT Code FROM DB1..tablec C WHERE Type=1 and code=a.code)
    
    Select A.code From DB2.dbo.tablea A
    Left Join DB1..tablec C On A.Code=C.Code and A.ID=C.ID and c.Type=1
    WHERE HeaderID=@HeaderID  
    and c.Code is null
    
    
    
    SELECT Code From DB2..tablea A WHERE HeaderID=@HeaderID AND Code  not in (SELECT Code FROM DB1..tablec C WHERE Type=1)
    
    
    SELECT Code From DB2..tablea A WHERE HeaderID=@HeaderID 
    except
    SELECT Code FROM DB1..tablec C WHERE Type=1

    Saturday, March 15, 2014 2:02 PM
    Moderator
  • Instead of checking later and raising an error you can create a check constraint on the table so that it raises error when you insert the non existent code itself.

    You can create a UDF like below

    CREATE FUNCTION CheckCodeExistence
    (
    @Code int,
    @HeaderID int
    )
    RETURNS bit
    AS
    BEGIN
    DECLARE @Ret bit
    
    SELECT @Ret = CASE WHEN NOT EXISTS (SELECT 1
    FROM DB2.dbo.TableC
    WHERE Code = @code
    AND [TYPE] = @headerID)
    THEN 0
    ELSE 1 END
    RETURN(@ret)
    END

    Then create a check constraint on Table A based on it as

    ALTER TABLE TableA ADD CONSTRAINT Chk_TableA CHECK (dbo.CheckCodeExistence(Code,HeaderID) =1 )

    Now when you try to insert record with Code 200 you'll get the below

    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the CHECK constraint "Chk_TableA". The conflict occurred in database "DB1", table "dbo.TableA".
    The statement has been terminated.
    


    See similar example here

    http://visakhm.blogspot.in/2012/05/implementing-multiple-table-based-check.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, March 15, 2014 6:13 PM