[kabuto178]

I need to select 2 rows from the members table to get name and id, and I need to get all rows from message table. Here is what I have so far any tips or help is appreciated

SELECT member_messages.message_to, 
       member_messages.message_from, 
       member_messages.message_time, 
       member_messages.message_body,
       members.display_name,members.id 
FROM members
INNER JOIN member_messages 
   ON member_messages.message_to = members.id  AS 'to'
INNER JOIN member_messages 
   ON member_messages.message_from = members.id AS 'from'
WHERE member_messages.message_to = '$userId'

This post has been edited by Atli: 09 January 2013 - 06:19 PM
Reason for edit:: Remember the [code] tags please.

[Atli]

You've got the JOINs reversed. The main focus of that query; the primary data you are getting, are the messages, so that should be the table used in the FROM clause. The "members" table is only used to fetch additional details about each message (the ID and name of the members involved), so those should be the joined tables.

Also, keep in mind that even though you are joining the same table twice, they will be considered separate tables as far as the result set is concerned. You'll need to use the aliases you create in the AS clause to distinguish between them in the field list, so make sure you don't quote the alias name! (Also, the alias should be on the table name, not the ON clause.)

That would be more like this:

SELECT 
    m.message,
    from.name AS sender,
    to.name AS recipicant
FROM messages AS m
JOIN members AS from
    ON m.from_id = from.id
JOIN members AS to
    ON m.to_id = to.id

This post has been edited by Atli: 09 January 2013 - 06:28 PM

[blackcompe]

You shouldn't embed variables directly into SQL statements, as it opens your system to injection attacks.

Quote

I need to select 2 rows from the members table to get name and id

Based on what criteria? Please clarify.

Quote

I need to get all rows from message table.

How about a select all query?

[kabuto178]

Thank you Atli! Good advice