locked
Select-Query RRS feed

  • 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

    Tuesday, April 14, 2009 5:31 AM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 14, 2009 7:15 AM

All replies

  • 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


    Tuesday, April 14, 2009 6:34 AM
  • 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.

    Tuesday, April 14, 2009 7:02 AM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 14, 2009 7:15 AM
  • 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)

     

    Tuesday, April 14, 2009 11:57 AM