|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
January 1st, 2005, 05:55 PM
|
|||
|
|||
Help with query
Hello,
I have 2 tables with one to many relationship. Image of tables and query . One table holds the messages sent to users, I want to show the LoginName in the User table instead of the UserID in my query, can anyone help me write the SQL. Code:
SELECT tblMessage.UserID, tblMessage.RecipientID, tblMessage.Subject, tblMessage.Message, tblMessage.DateSent, tblUser.LoginName FROM tblMessage INNER JOIN tblUser ON tblMessage.UserID = tblUser.UserID;
|
|
#2
January 1st, 2005, 06:19 PM
|
||||
|
||||
|
your query looks okay to me
alternatively, if you want all users, whether or not they have any messages, you may want a LEFT OUTER JOIN Code:
select tblUser.LoginName
, tblMessage.RecipientID
, tblMessage.Subject
, tblMessage.Message
, tblMessage.DateSent
from tblUser
left outer
join tblMessage
on tblUser.UserID
= tblMessage.UserID
|
|
#3
January 1st, 2005, 07:49 PM
|
|||
|
|||
|
Thanks for the response rudy.
I tried the left outer join as you suggested but on the recipientID I still see the UserID, is there a way of showing the much readable LoginName. Thanks
|
|
#4
January 1st, 2005, 08:08 PM
|
||||
|
||||
|
ah, you want the recipient's name, you didn't say that the first time
 Code:
select U1.LoginName as Sender
, U2.LoginName as Recipient
, tblMessage.Subject
, tblMessage.Message
, tblMessage.DateSent
from tblUser as U1
left outer
join tblMessage
on U1.UserID
= tblMessage.UserID
left outer
join tblUser as U2
on tblMessage.RecipientID
= U2.UserID
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Help with query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
