Page 1 of 1

Join me on INNER JOIN!

Posted: Sat Feb 25, 2006 8:14 am
by gotlisch
Hi,

I'm having some problems with a SQL query. The thing is that I need to join a table "users" with two tables...which means that it won't be unique because user_name will occur more than once....how can I avoid this problem. I would like to retriev the data using only one query if it's possible. I have put the incorrect syntax in brackets...but left it in to show what I need to do.

Code: Select all

SELECT users.user_name, spotlight.message,  reply.message
FROM spotlight INNER JOIN reply ON spotlight.id = reply.s_id 
INNER JOIN users ON users.id = reply.user_id
(INNER JOIN users ON spotlight.user_id = users.id)
WHERE spotlight.id=1

Cheers

Mikael

Posted: Sat Feb 25, 2006 8:28 am
by Gambler

Code: Select all

SELECT u1.user_name, s.message,  r.message
FROM spotlight AS s 
INNER JOIN reply AS r ON s.id = r.s_id 
INNER JOIN users AS u1 ON u1.id = r.user_id
INNER JOIN users AS u2 ON s.user_id = u2.id
WHERE s.id=1

Posted: Sat Feb 25, 2006 8:45 am
by gotlisch
Thanx! :D