Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: SQL guidance please

    [PROFILE] id, email, username
    [MAILBOX] id, mail_from, mail_to, mail_subject, mail_body

    Here are two tables from the database. I save the PROFILE.id in the MAILBOX.mail_from and mail_to.

    Now, my problem is that I have no idea how to read the MAILBOX data efficiently since I need to retrieve the correct profile username for both the mail_from and the mail_to.

    I plan to use the SQL on a CLASSIC ASP file.

    Thank you for reading =)

  2. #2
    Join Date
    May 2009
    Posts
    4
    SELECT Mailbox.id, (select Profile.nickname from Profile where Profile.id=Mailbox.mail_from), (select Profile.nickname from Profile where Profile.id=Mailbox.mail_to)
    FROM Mailbox;
    Is this the proper way to do it?

  3. #3
    Join Date
    May 2009
    Posts
    4


    I have two table, one with the user id and nicknames (Profile) and another one with a list of messages sent from one user to another user (Mailbox).

    The Mailbox tables stores the id of the user sending a message and the id of the user recieving that message. My problem is to get the proper nicknames of those users...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Mailbox.id
         , p_from.nickname as name_from 
         , p_to.nickname   AS name_to
      FROM Mailbox
    INNER
      JOIN Profile AS p_from
        ON p_from.id = Mailbox.mail_from
    INNER
      JOIN Profile AS p_to
        ON p_to.id = Mailbox.mail_to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    4
    Quote Originally Posted by r937
    Code:
    SELECT Mailbox.id
         , p_from.nickname as name_from 
         , p_to.nickname   AS name_to
      FROM Mailbox
    INNER
      JOIN Profile AS p_from
        ON p_from.id = Mailbox.mail_from
    INNER
      JOIN Profile AS p_to
        ON p_to.id = Mailbox.mail_to
    Thank you very much r937! =)
    In the meantime, I also found my way to the "inner join" and I also found out that I could drop the "mail-to" part in the Mailbox since it's an information I already have in my asp session variables so here is my select:
    SELECT Mailbox.id
    , Mailbox.mail_from AS Mailbox_mail_from
    , Mailbox.mail_to
    , Mailbox.mail_subject
    , Mailbox.mail_time
    , Mailbox.mail_to_folder
    , Mailbox.mail_to_status
    , Profile.id AS Profile_id
    , Profile.nickname
    FROM Mailbox
    INNER
    JOIN Profile
    ON Mailbox.mail_from = Profile.id
    Can you explain me how ACCESS deals with the INNER JOIN so I can try to understand what it may cost to use it in terms of performances compared to multiple SELECTS?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mololo
    Can you explain me how ACCESS deals with the INNER JOIN so I can try to understand what it may cost to use it in terms of performances compared to multiple SELECTS?
    i'm not sure i understand the question

    a join is always more efficient than if you were to issue separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •