Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vision.v1
Yak Posting Veteran
72 Posts |
 Posted - 2011-04-12 : 03:51:07
|
| Hi, Below is the #temp table from which i want to find only those employees having only bonusType 'B3' but not otherbonuses.In the below case only employee 10 should be displayed.IF OBJECT_ID('tempdb.dbo.#tempBonuses') IS NOT NULLDROP TABLE #tempBonusesCREATE TABLE #tempBonuses( empno INT, bonusType VARCHAR(50), amount MONEY)INSERT INTO #tempBonuses VALUES(1,'B1',100)INSERT INTO #tempBonuses VALUES(1,'B2',200)INSERT INTO #tempBonuses VALUES(1,'B3',50)INSERT INTO #tempBonuses VALUES(2,'B1',100)INSERT INTO #tempBonuses VALUES(3,'B2',200)INSERT INTO #tempBonuses VALUES(2,'B3',50)INSERT INTO #tempBonuses VALUES(8,'B1',100)INSERT INTO #tempBonuses VALUES(9,'B2',200)INSERT INTO #tempBonuses VALUES(10,'B3',50) |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-04-12 : 04:05:35
|
select *from #tempBonuses t1where bonusType = 'B3'and not exists (select * from #tempBonuses t2 where t2.empno=t1.empno and t2.bonusType <> 'B3')
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
|
|
Query