Last post Apr 14, 2009 11:57 AM by hans_v
Apr 14, 2009 05:31 AM|Tigers21|LINK
Need help with a select-query:
I got three tables:
userID (Primary Key)
userID (Foreign key)
mailID (Foreign key)
isTransmitter (if false, the user is reciever)
mailID (Primary key)
I need to select tblMail.message for a "reciever" (isTransmitter = false) with userID = 20...In the same query I also want to select the transmitter-username
Apr 14, 2009 06:34 AM|mohd786hussain|LINK
try the following query :
select tblMail.message ,tblMember.username from tblMail
inner join tblMemberMail on tblMail.mailID=tblMemberMail.mailID
inner join tblMember on tblMemberMail.userID=tblMember.userID
where tblMember.userID='20' and tblMemberMail.isTransmitter=1
Apr 14, 2009 07:02 AM|Tigers21|LINK
But what if I want all messages from tblMail that belongs to userID 20 and the username of the transmitter for each message.
Apr 14, 2009 07:15 AM|mohd786hussain|LINK
for getting transmitter for each message .
you has to create a function in sql server which will return you the transmitter name.
Apr 14, 2009 11:57 AM|hans_v|LINK
To answer the question, maybe it's better to reconsider the tables structurs. It seems strange to create records for both the receiver(s) and the transmitter of the email in tblMemberMail. A mail is send by only 1 member, so why not put the userID of the
transmitter in tblMail?
userID (FK) 'This is the transmitter
userID (FK) 'This is the receiver