how to check, one record in one table occurs how m

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)

how to check, one record in one table occurs how m

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-09-10 : 15:29:11

how to check, one record in one table occurs how many number of times in another table ?????

Hello All,

i have below two tables in my database,

TempStudentTable

StID,Name,Fee
100,Mark,2500
101,Alan,1500
102,Stev,1000
103,King,3000
104,Kara,4000
105,Limb,2500
106,Trac,1500
107,Kilda,3000

OrigStudentTable

StID,Name,Fee
100,Mark,2500
101,Alan,1500
101,Alan,1500
102,Stev,1000
103,King,3000
103,King,3000
105,Limb,2500
101,Alan,1500
106,Trac,1500
101,Alan,1500
101,Alan,1500
103,King,3000
100,Mark,2500

first table data needs to be check on OrigStudentTable data, to find out each tempStudentTable record has how many number of records it has in OrigStudentTable
it needs to be match whole record with both table

any ideas,
Please Kindly Help Me
Thanks in advance

Best Regards
Dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-09-10 : 15:42:17

Please any ideas

Thanks in advance!!!

the result will be

StID,Name,Fee,Occurs
100,Mark,2500,2
101,Alan,1500,5
102,Stev,1000,1
103,King,3000,3
104,Kara,4000,0
105,Limb,2500,0
106,Trac,1500,1
107,Kilda,3000,0

Please

Thanks in advance
mohini

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-10 : 15:56:41

select B.StID,b.Name,b.Fee, count(*)
from
TempStudentTable A
join OrigStudentTable B on A.StID = B.StID and A.Name = b.Name and A.Fee = b.Fee
group by B.StID,b.Name,b.Fee

An infinite universe is the ultimate cartesian product.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-10 : 15:56:53

[code]declare @TempStudentTable table (StID int, [Name] varchar(255), Fee int)
declare @OrigStudentTable table (StID int, [Name] varchar(255), Fee int)

insert @TempStudentTable
select 100,'Mark',2500 union all
select 101,'Alan',1500 union all
select 102,'Stev',1000 union all
select 103,'King',3000 union all
select 104,'Kara',4000 union all
select 105,'Limb',2500 union all
select 106,'Trac',1500 union all
select 107,'Kilda',3000

insert @OrigStudentTable
select 100,'Mark',2500 union all
select 101,'Alan',1500 union all
select 101,'Alan',1500 union all
select 102,'Stev',1000 union all
select 103,'King',3000 union all
select 103,'King',3000 union all
select 105,'Limb',2500 union all
select 101,'Alan',1500 union all
select 106,'Trac',1500 union all
select 101,'Alan',1500 union all
select 101,'Alan',1500 union all
select 103,'King',3000 union all
select 100,'Mark',2500

select o.StID,o.[Name],o.Fee,count(*)
from @OrigStudentTable o
where exists (select * from @TempStudentTable t where t.StId=o.StId and
t.[Name] = o.[Name] and
t.Fee = o.Fee)
group by o.StID,o.[Name],o.Fee[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

dhani
Posting Yak Master

132 Posts

Posted - 2009-09-10 : 16:17:56

Hi Webfred,

I Great ful to you

Thank you very much jesus.,,,,,,,

Best Regards
mohini

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-10 : 16:46:56

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-10 : 17:43:15

Webfred's solution is more efficient with the test data. Generally, I stick with joins unless there is an overriding need to do something "fancy".

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources