Multiple FKs of the same PK problem!

Hi SQL folks,

I have a fuzzy problem with my application,

I'm building a small internal messaging system. I have two tables: SysUsers and mail tables.

I need to select both the "mailFrom" and "mailTo" from the mail table - both of them is a uniqueidenifier and are FK from the UserId in the SysUsers table- , and then get the correspoding UserName from the SysUsers table, how can I do that?

Here is my query:

/*Get the UserName of both the Sender and Reciever*/
SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo, SysUsers.UserName
FROM mail INNER JOIN
SysUsers ON mail.mailTo= SysUsers.UserId
AND mail.mailFrom= SysUsers.UserId

/*Get only the current users mails*/
WHERE (SysUsers.UserId = @.UserId)

Hello Jocker,

You have to define two inner join to the same table with different aliasses.

SELECT ... , MailTo.UserName, MailFrom.UserName

FROM mail

INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo

INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom

WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)

|||

You need to alias the Sysusers Table

SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId

WHERE (S1.UserId = @.UserId)

|||

jeroenm:

Hello Jocker,

You have to define two inner join to the same table with different aliasses.

SELECT ... , MailTo.UserName, MailFrom.UserName

FROM mail

INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo

INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom

WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)

Hi jepenm,

Thanks for you responce :)

|||

TATWORTH:

You need to alias the Sysusers Table

SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId

WHERE (S1.UserId = @.UserId)

Hi TATWORTH,

Thanks for your response

Multiple Attachments

How do I get multiple attachments into one mail note. I have five reports
that I what my uses to receive in the form of Excel and PDF attachments and I
do not want them to get five Emails. Is this possible with subscriptions
Thanks
DougYou need to set up a master report that contains the other reports as sub
reports. This is fairly easy to do. The master report can be blank or can
have links to jump to particular sub reports. Note that if you have headers
and footers in your sub reports you might need to tweek them a little to get
it to look just the way you want. Also, you might need to spend a little
effort to make sure that page breaks happen in the right spots if you are
exporting to a format like PDF.
"dlong" wrote:
> How do I get multiple attachments into one mail note. I have five reports
> that I what my uses to receive in the form of Excel and PDF attachments and I
> do not want them to get five Emails. Is this possible with subscriptions
> Thanks
> Doug
>

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
GerhardHi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
more[vbcol=seagreen]
>|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
> the
> more
>

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
Gerhard
Hi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>
|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
more
>
|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
first
> the
> more
>

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
GerhardHi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> > Using database mail. It works using a single email address for an
> > operator.
> > Adding a second email address after a semicolon still sends to the first
> > email address but not the second. Separating them with a comma breaks
the
> > whole thing, no email is sent.
> > Does this mean I have to create a dl in Exchange, if I need to notify
more
> > than one person for a given event?
> > Gerhard
> >
> >
>|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> > Hi Gerhard,
> >
> > That's usually the easiest way. It also allows who is on the list to be
> > managed externally easily.
> >
> > HTH,
> >
> > Greg
> >
> > "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> > news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> > > Using database mail. It works using a single email address for an
> > > operator.
> > > Adding a second email address after a semicolon still sends to the
first
> > > email address but not the second. Separating them with a comma breaks
> the
> > > whole thing, no email is sent.
> > > Does this mean I have to create a dl in Exchange, if I need to notify
> more
> > > than one person for a given event?
> > > Gerhard
> > >
> > >
> >
> >
>