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

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

March 15th, 2014 8:07am

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



Free Windows Admin Tool Kit Click here and download it now
March 15th, 2014 8:12am

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

March 15th, 2014 10:08am

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

Free Windows Admin Tool Kit Click here and download it now
March 15th, 2014 2:19pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics