got stuck with simple tsql - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

got stuck with simple tsql

Author

Topic

sks198117
Starting Member

46 Posts

Posted - 2010-12-08 : 15:06:14

Hi friends i got stuck with simple query please help me out
below are the table and data
--------------
declare @tbl1 table
(
c1 int,
c2 int
)
insert into @tbl1 values(1,1)
insert into @tbl1 values(1,2)
insert into @tbl1 values(1,3)
insert into @tbl1 values(1,5)
insert into @tbl1 values(1,4)
insert into @tbl1 values(1,6)
insert into @tbl1 values(1,7)
insert into @tbl1 values(2,1)
insert into @tbl1 values(2,2)
insert into @tbl1 values(2,8)
--select * from @tbl1
select t1.c1 from @tbl1 t1,@tbl1 t2 where (t1.c2=1 and t2.c2=8)
-----------------------------------
now its giving me output as 1 and 2 but i want output as only 2 because the value of c2 is 1 and 8 for 2 only.

Please help

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-08 : 15:18:02

[code]declare @tbl1 table
(
c1 int,
c2 int
)
insert into @tbl1 values(1,1)
insert into @tbl1 values(1,2)
insert into @tbl1 values(1,3)
insert into @tbl1 values(1,5)
insert into @tbl1 values(1,4)
insert into @tbl1 values(1,6)
insert into @tbl1 values(1,7)
insert into @tbl1 values(2,1)
insert into @tbl1 values(2,2)
insert into @tbl1 values(2,8)
--select * from @tbl1
~~select t1.c1 from @tbl1 t1,@tbl1 t2 where (t1.c2=1 and t2.c2=8)~~

SELECT t1.c1
FROM @tbl1 t1
JOIN @tbl1 t2
On t1.c1 = t2.c1
WHERE t1.c2 = 1
AND t2.c2 = 8[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-09 : 12:15:42

you dont need join. you can just do


SELECT	c1
FROM	@tbl1
WHERE c2 IN (1,8) 
GROUP BY c1
HAVING COUNT(DISTINCT c2)=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources