SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to JOIN twice?

    I have a simple table called 'msg' like this:

    USER_TO | USER_FROM | MSG
    1 | 5 | hello john

    I want to do a lookup of the 'user' table that contains these values:

    USER_ID | NAME
    1 | bob
    2 | sally
    5 | john

    desired output:

    USER_TO | USER_FROM | MSG
    bob | john | hello john

    I tried to use two LEFT JOIN's but it threw an error saying the user field is not unique. What is the correct SQL to use here to lookup the values of both USER_TO and USER_FROM?

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select t.name as user_to,
             f.name as user_from,
             msg.msg
      from user as t
      join msg on t.user_id = msg.user_to
      join user as f om msg.user_from = f.user_id

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cant quite get it to work, as my query is a bit more complex:

    Code MySQL:
    INSERT INTO z_request (
    SELECT t.REQ_CO, t.REF_CO, t.REQ_TITLE, f.USER_NA as reporter, t.USER_NA as assignee, t.PRIORITY_CO, t.RESULT_REPORT
    FROM t_request AS t
     JOIN m_user ON t.USER_FROM = m_user.USER_CO
     JOIN m_user AS f ON t.USER_RESPONSE = m_user.USER_CO
    )

    error is: #1054 - Unknown column 't.USER_NA' in 'field list'

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok got it working now, thanks for the query help.


Bookmarks

Posting Permissions

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