[SOLVED] Problem with SELECT

[mattblack_uk2002]

I'm quite new to MySQL and struggling with a MySQL SELECT Statement.

 

I'm working with 3 tables which are as follows:

 

Table 1

User ID

Username

 

Table 2

Message ID

Author ID (linked to 1.User ID)

Subject

Message Text

 

Table 3

Message ID

Author ID

Reciever ID

 

These are obviously not the complete tables however they will suffice to explain my problem. I should let you know that I have to work with these and cannot amend them in any way.

 

I would like to do a SELECT that will give me the author username, reciever username, subject, and text

 

however according to my limited knowledge, this would work out as follows:

 

SELECT Table1.username, Table1.username, Table1.subject etc....

 

So my question is how can I tell MySQL what to associate with each instance of username?

[bubblegum.anarchy]

Using joins and aliases something like this:

 

SELECT Message_Text
   , Author.Username
   , Receiver.Username
FROM Table_2 AS 
   INNER JOIN Table_3 ON Table_2.Message_ID = Table_3.Message_ID
   INNER JOIN Table_1 AS Author ON Table_3.Author_ID = Author.User_ID
   INNER JOIN Table_1 AS Receiver ON Table_3.Receiver_ID = Receiver.User_ID

[mattblack_uk2002]

I get

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN Table_3 ON Table_2.Message_ID = Table_3.' at line 1

 

(I have subsituted the table names for the examples that you supplied)

 

I am using MySQL 4.1...

[paul2463]

remove the word AS from the query after table_2

[mattblack_uk2002]

Works a treat.... thanks.