return count result... - 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)

return count result...

Author

Topic

tran008
Starting Member

38 Posts

Posted - 2007-05-15 : 09:42:32

hi all,

I'm tryping to get the information from 1 table that having more then 1 matching from antother table and display the information from the matching table, eg:

tbl1 tbl2
1|2 2|B
1|3 2|C
2|2 4|A
3|4 4|N
3|1 1|A

result
2|B
2|C
4|A
4|N

thanks...

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-15 : 09:55:57

try this ...

Select t2.num1, t2.val from
tbl1 as t1, tbl2 as t2
where t2.num1 = t1.num2 or t2.num1 = t1.num1
group by t2.num1, t2.val
having count(t2.val) < 3
order by t2.num1

Mahesh

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-15 : 12:01:03

[code]
Declare @tbl1 table (col1 int, col2 int)
insert into @tbl1 values ( 1,2)
insert into @tbl1 values ( 1,3)
insert into @tbl1 values ( 2,2)
insert into @tbl1 values ( 3,4)
insert into @tbl1 values ( 3,1)
Declare @tbl2 table (col1 int, col2 varchar(2))
insert into @tbl2 values (2,'B')
insert into @tbl2 values (2,'C')
insert into @tbl2 values (4,'A')
insert into @tbl2 values (4,'N')
insert into @tbl2 values (1,'A')
/*
tbl1 tbl2
1|2 2|B
1|3 2|C
2|2 4|A
3|4 4|N
3|1 1|A

result
2|B
2|C
4|A
4|N
*/

select * from @tbl2 t
where t.col1 in (
select t1.col2 from @tbl1 t1
join @tbl2 t2 on t1.col2 = t2.col1
group by t1.col2
having count(*) > 1
)
[/code]

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources