3 Replies - 2052 Views - Last Post: 18 January 2013 - 08:29 PM

#1 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Multiple Inner Joins

Posted 09 January 2013 - 06:07 PM

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.


Is This A Good Question/Topic? 0
  • +

Replies To: Multiple Inner Joins

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,964
  • Joined: 08-June 10

Re: Multiple Inner Joins

Posted 09 January 2013 - 06:27 PM

*
POPULAR

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

Was This Post Helpful? 5
  • +
  • -

#3 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1150
  • View blog
  • Posts: 2,528
  • Joined: 05-May 05

Re: Multiple Inner Joins

Posted 09 January 2013 - 06:29 PM

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?
Was This Post Helpful? 3
  • +
  • -

#4 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Re: Multiple Inner Joins

Posted 18 January 2013 - 08:29 PM

Thank you Atli! Good advice
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1