Query for sending emails for users

Transact-SQL
1

Hi everybody,
I hope I can describe what I need to do
I’ve two tables one for users and their emails (users)
The other one shows tasks for each user and his responsible supervisor (tasks)
What I need, to run a query on the (tasks) table to send an email for each supervisor contains list of his users and their tasks.
May anybody assist on how to do it?
Below is the script for the two tables.

CREATE TABLE [dbo].[users](
[user] varchar(20) NULL,
[email] varchar(20) NULL,
) ON [PRIMARY]

INSERT INTO [users] ([user], [email]) values ('user1','user1@abc.com');
INSERT INTO [users] ([user], [email]) values ('user2','user2@abc.com');
INSERT INTO [users] ([user], [email]) values ('user3','user3@abc.com');
INSERT INTO [users] ([user], [email]) values ('user4','user4@abc.com');
INSERT INTO [users] ([user], [email]) values ('user5','user5@abc.com');
INSERT INTO [users] ([user], [email]) values ('user6','user6@abc.com');

CREATE TABLE [dbo].[tasks](
[user] varchar(20) NULL,
[superv] varchar(20) NULL,
[task] varchar(20) NULL,
) ON [PRIMARY]

INSERT INTO [tasks] ([user], [superv],[task]) values ('user5','user1','task52');
INSERT INTO [tasks] ([user], [superv],[task]) values ('user4','user1','task41');
INSERT INTO [tasks] ([user], [superv],[task]) values ('user3','user2','task31');
INSERT INTO [tasks] ([user], [superv],[task]) values ('user3','user2','task32');
INSERT INTO [tasks] ([user], [superv],[task]) values ('user5','user1','task51');
INSERT INTO [tasks] ([user], [superv],[task]) values ('user6','user2','task61');
INSERT INTO [tasks] ([user], [superv],[task]) values ('user6','user2','task62');

2

hi

hope this link helps ..

3

please let me know if I need to write the SQL

Would be glad to

:+1:

4

yes please , appreciate it, will save lot time to tries

5

depends how you would like to do it

via purely sql server?
https://www.sqlshack.com/configure-database-mail-sql-server/

via a powershell script?

via python?

via SSIS?

Which one are you more comfortable with

6

i thought about it ..

T-SQL how do you want the result Set ??

7

via purely T-SQL

8

https://www.sqlshack.com/configure-database-mail-sql-server/

9

I did configured the sql email and tested, but still facing difficulties on doing the query.

10

What have you tried so far - and where are you having issues?

11

i failed to get the result as below
task user supervisor supervisorEmail
can't get supervisor email

12

What @jeffw8713 is asking you is to show what query you have done and what the issue is

13

SELECT dbo.users.[user], dbo.tasks.task, dbo.tasks.superv, (select dbo.users.email where dbo.tasks.superv=dbo.users.[user]) as supervemail
FROM dbo.tasks CROSS JOIN
dbo.users

also tried right and left joint
it always gives lot of nulls or false counts

14

Given the data you provided - what is your expected result? And how will these results be emailed?

15

it seems that i did not describe what i need well
i expect the result to be list of users with their tasks and the responsible supervisor with his email
then to send an email for each supervisor shows list of his users with their tasks
i hope that i clarified my issue

16

please see if this helps

select 
     a.[user]
   , b.task
   , b.superv 
   , c.email 
from  
    users a 
	   join 
    tasks b 
	    on a.[user] = b.[user]
       join 
	users c 
	    on b.superv = c.[user]
order by 1

image

17

Hi

No response

:grin::grin:

18

Hi harishgg1
sorry, I got busy with the second part of the query and trying to send a message with the resulting data for each one.
thank you for your assist. :grinning:

19

hi

whats your feedback on what i did

is it correct ? just curious
:wink:

20

:+1: