September 15, 2009 at 3:05 am
Hi All
I have a table as below
Create Table #Temp1 ( ID int,Dep int,Key int)
Insert into #temp (1,2,123)
Insert into #temp (1,2,124)
Insert into #temp (1,2,125)
Insert into #temp (1,2,0)
Insert into #temp (1,3,0)
Insert into #temp (1,4,1)
Insert into #temp (1,4,2)
Insert into #temp (1,4,3)
Insert into #temp (1,5,2)
Insert into #temp (1,6,0)
I need a result as
1 3 0
1 6 0
In other words all the rows that has only 0 as Key, its a self join ( if i am correct) but i am not sure how to do it, please guide me on this
Thanks in advance
September 15, 2009 at 3:23 am
select * from #Temp1
where [Key] = 0
September 15, 2009 at 3:30 am
select t.* from #temp t
inner join (select ID,DEP from #temp
group by ID,DEP
having max()=0) t1 on t1.id=t.id and t1.dep=t.dep
September 15, 2009 at 6:06 am
SELECT ID, Dep, [Key]
FROM #Temp t
WHERE [Key] = 0 AND NOT EXISTS
(SELECT *
FROM #Temp
WHERE [Key] t.[Key] AND Dep = t.Dep)
--Jonathan
September 15, 2009 at 6:37 am
thanks a lot guys, this worked, thanks for your help 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply