can't join this query

Dev Shed Forums Sponsor:

July 21st, 2004, 11:18 PM

lionslair

Registered User

Join Date: Jul 2004

Location: Perth, Western Australia

Posts: 9

Time spent in forums: < 1 sec
Reputation Power: 0

can't join this query

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.

July 28th, 2004, 12:49 PM

brouse

Contributing User

Join Date: Jul 2004

Location: Atlanta, GA

Posts: 119

Time spent in forums: 22 h 37 m 39 sec
Reputation Power: 9

Are you trying to get both the original author and the edit author? This may (or may not) work:

Quote:

select n.*, CONCAT(u1.user_fname, " ", u1.user_lname) as org_author, CONCAT(u2.user_fname, " ", u2.user_lname) as edit_author
FROM news n, users u1, users u2
WHERE u1.user_id = n.news_author
and u2.user_id = n.edit_author

July 28th, 2004, 09:57 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,965

Time spent in forums: 1 Month 4 Weeks 16 h 55 m 24 sec
Reputation Power: 1024

look, lionslair, this is what happens when you cross post (which is not allowed)

somebody answers one of the copies of the post in one forum -- http://forums.devshed.com/showthread.php?t=167409

and then later, somebody else spends a lot of time answering the same goldurned question in another forum -- this one

so it would behoove you to politely close each of your open threads if the answer has in fact been provided

don't forget this one -- http://forums.devshed.com/showthread.php?t=167408

and please don't do that again

__________________
r937.com | rudy.ca

pre-order my book Simply SQL from Amazon

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: