February 7, 2017 at 2:53 pm
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
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
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