Select-Query

Question

User-1221430945 posted

Need help with a select-query:

I got three tables:

tblMember:
userID (Primary Key)
username (string)

tblMemberMail
userID (Foreign key)
mailID (Foreign key)
isTransmitter (if false, the user is reciever)

tblMail
mailID (Primary key)
message

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

Answer 1

User1872574491 posted

Hi,

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

Answer 2

User-1221430945 posted

thanks,

But what if I want all messages from tblMail that belongs to userID 20 and the username of the transmitter for each message.

Answer 3

User1872574491 posted

Hi,

for getting transmitter for each message .

you has to create a function in sql server which will return you the transmitter name.

Answer 4

User-1199946673 posted

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?

tblMail
mailID (PK)
userID (FK) 'This is the transmitter
message

tblReceivers
userID (FK) 'This is the receiver
mailID (FK)