how to retrieve number of rows in joins

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

General SQL Server Forums

New to SQL Server Programming

how to retrieve number of rows in joins

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-04-22 : 07:25:34

Hai All

This is my query
select e.EmpId, e.EmpName, d.DeptName from Emp e
inner join Department d on d.EmpId = e.EmpId

Now, I want to get, how many rows it is retrieving in that query

plz help me....

Thanks in Advance

Suresh Kumar

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 07:32:17

select e.EmpId, e.EmpName, d.DeptName from Emp e
inner join Department d on d.EmpId = e.EmpId

select @@rowcount

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-04-22 : 08:29:47

My query returns the following data

101 suresh suresh@mail.com Maths
102 ganesh ganesh@mail.com Physics
103 kamesh kamesh@mail.com Chemistry
104 ramesh ramesh@mail.com Phyics
105 mahesh mahesh@mail.com Maths

Now, I want to send mail individually, by getting the mail id from each row
please solve this query

Suresh Kumar

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-22 : 08:41:40

Are You Sending mail by your SQL Server...
or you sending mail by your application.....

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-04-22 : 08:42:51

am send mail through sql server.

How to get individual row value

Suresh Kumar

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-22 : 08:46:48

Cursor :(

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-22 : 08:52:52

i thought for cursor for this,,,,,,,
but may be there is some other way to do so...
let see what Experts say about that...
but i fell cursors or alternatively i thought of temporary table...
let see....

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-04-22 : 08:58:42

ya, I too thought of using cursors
But I should not use cursors and even temporary tables also

Suresh Kumar

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 08:59:24

I would go for
select e.EmpId, e.EmpName, d.DeptName
into #a
from Emp e
inner join Department d on d.EmpId = e.EmpId, @DeptName bvarchar(100)

declare @EmpId int, @EmpName varchar(100),
select @EmpId = 0
while @EmpId < (select max(EmpId) from #a)
begin
select top 1 @EmpId = EmpId, @EmpName = EmpName, @DeptName = DeptName
from #a where EmpId > @EmpId order by EmpId
exec sp_send @EmpName ...
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-22 : 09:02:38

yea thats wat i think that u use temporary table to do so...
as NR Sir suggest to you.....
and what is the problem with using temporary table in it i think its ok to use u temporary table...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-04-22 : 09:04:56

I assume your query is missing an email column
Try this

declare @all_emails varchar(8000)
select @all_emails = IsNull(@all_emails, '') + e.email + ';'
from Emp e
inner join Department d on d.EmpId = e.EmpId

But you will hit a limit on the number of emails that can be put into the varchar sooner or later.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-04-22 : 09:07:27

I dont the problem in using temporary table, but my requirement is like that. I should not use temporary tables and cursors

Suresh Kumar

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-22 : 09:19:05

then just wait for some other suggestions ,,,,
hope u will get your solution....

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 09:20:12

Would you like to rephrase that?
No idea what you mean.
Think you are saying you don't want to use a temp table or cursor.
How about a table variable?
Otherwise if the email you are sending has the same contents go with jhocutt suggestion with a slight amendment.

declare @all_emails varchar(max)
select @all_emails = coalesc(@all_emails + ';','') + e.email
from Emp e
inner join Department d on d.EmpId = e.EmpId

Do you need the department?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-04-22 : 09:39:15

also and I need to retrieve EmpId along with Email

Suresh Kumar

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-04-22 : 10:01:14

Seems we are having trouble trying to figure out what you want.
Try posting the question your prof asked.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-22 : 10:17:11

u just use a temp table to do so,,
i think ..

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 10:33:03

If you need the emp_id then it will be a different email for each ercipient - to do that in sql server you will need to loop.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-04-23 : 07:50:45

Here is the solution, without using temp and cursors

declare @EmpId int
declare @EmailId varchar(50)
declare @CurrId int
declare @MaxId int

select top 1 @EmpId = e.EmpId, @EmailId = e.Email from Emp e
inner join Dept d on d.DeptId = e.DeptId order by EmpId desc

print @EmpId
print @EmailId

select @MaxId = max(EmpId) from Emp
set @CurrId = @MaxId + 1
while @CurrId > @MaxId
begin
set @CurrId = @MaxId
select top 1 @MaxId = e.EmpId, @EmailId = e.Email from Emp e
inner join Dept d on d.DeptId = e.DeptId
where e.EmpID < @CurrId
order by e.EmpId desc
print @MaxId
print @EmailId
end

Suresh Kumar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources