Post reply

Exists Condtion

  • February 7, 2017 at 2:53 pm

    #318581

    hi,

    Below the sample to play with
    Declare @test-2 table(id int, VendorID int, CompanyId int);
    insert into @test-2
    select 1,1000,null union all
    select 2,1000,1001

    Query :

    select id from @test-2 T

    where exists(select 1 from @test-2 T1 where t1.id = T.id and VendorID = @VendorID and CompanyId = @CompanyId)
    or
    exists(select 1 from @test-2 T1 where t1.id = T.id and VendorID = @VendorID and CompanyId is Null)

    the logic what am trying is if the companyID data exits in the table then bring the id belongs to it. if not then check for vendor id exists then bring the id related to it and  if both was not there then return 0 

    Declare @VendorID int = 1000, @CompanyId int = 1001   --- output has to be 2
    Declare @VendorID int = 1000, @CompanyId int = null-- output has to be 1

    Am not sure where am i doing mistake. Any suggestion or correction please.

  • February 7, 2017 at 3:08 pm

    #1927216

    select top (1) id
    from @test-2 T
    where VendorID = @VendorID and (CompanyId = @CompanyId Or CompanyId IS NULL)
    order by companyid desc

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • February 7, 2017 at 7:23 pm

    #1927227

    thank you scott.

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

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