SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Fully Sweet Car noddy's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    759
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with a join statement

    I didnt get any help on this from another thread so this is mysql I'll try it here.

    I really need some help. I'm going nuts. I been trying to get this to work all morning and i cant get it it work. the simple version is I am trying to join these two sql queries and make them one.

    Code:
    SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
    FROM news
    LEFT JOIN users ON news.news_author = users.user_id
    WHERE news_id =41
    
    SELECT CONCAT('users.user_fname', 'users.user_lname')  AS edit FROM users WHERE users.user_id=54
    my previous attempts are

    trouble with query.

    I have a news table which has the ID of the original author and a column for the author who last edits the news article. both columns store the id of the author.

    now when I'm editing the article I want to get the name of the author from the users table.

    I have done a left join to get the first author (original author) but I'm having a real hard time trying to get the name of the author who last edited the record.

    they both need to lookup the user_fname and user_lname fields from the users table.

    my current sql is below

    Code:
    SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41
    which gives me
    Code:
    news_id 	news_subject 	news_article 	news_author 	news_date 	news_edited 	edit_author 	org_author 	
    41	Interclub	Bunbury IC club has asked us all to attend a inter...	77	1090247547	1090418362	54	Adam Green
    Now how do i adapt it to get the name of the author to last edit the article? do I do a sub query another left join ???
    every way I try doesn't work.

    this is what I'm currently trying to get it with.

    Code:
    SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
    FROM news
    LEFT JOIN users ON news.news_author = users.user_id
    LEFT JOIN (
    
    SELECT CONCAT( users.user_fname, " ", users.user_lname ) AS edit_author
    FROM news
    WHERE news.edit_author = users.user_id
    )
    WHERE news_id =41

    Heres a different approach that I thought would work

    Code:
    SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
    FROM news
    LEFT JOIN users ON news.news_author = users.user_id
    LEFT JOIN (
    
    SELECT concat( users.user_fname, users.user_lname ) AS edit
    FROM users
    WHERE users.user_id =54
    ) AS edit
    WHERE news_id =41
    but no i get this error
    Code:
    MySQL said: 
    
    #1064 - You have an error in your SQL syntax near '( 
    SELECT concat( users.user_fname, users.user_lname )  AS edit
    FROM users
    WHERE' at line 4
    I also tried

    Code:
    SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author,  (SELECT concat(users.user_fname, users.user_lname) AS edit from users where users.user_id=54) AS edit FROM news LEFT JOIN users ON news.news_author=users.user_id 
    WHERE news_id=41
    but got this error
    Code:
    MySQL said: 
    
    #1064 - You have an error in your SQL syntax near 'SELECT concat( users.user_fname, users.user_lname )  AS edit
    FROM users
    WHERE us' at line 2
    can anyone help me with this query its been driving me up the wall all morning.

  2. #2
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT news.*, 
    	CONCAT(users_auth.user_fname, " ", users_auth.user_lname ) AS original_author
    	CONCAT(users_edit.user_fname, " ", users_edit.user_lname)  AS editing_author
    FROM news
    LEFT JOIN users AS users_auth ON news.news_author = users_auth.user_id
    LEFT JOIN users AS users_edit ON news.edit_author = users_edit.user_id
    WHERE news_id=41;


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
  •