-1

So, I'm using PHP and MySQL. I have a table 'notifications' and a table 'users'. The notification gives a sender and a recipient. I've done some research and tried some things out, but I couldn't come to a solution. The following SQL query gives me the data set in the notification and also the recipient data, but how can I also select the sender data from the users table in one query?

SELECT notifications.id,notifications.recipient_id,notifications.sender_id,notifications.unread,notifications.type,notifications.parameters,notifications.created_at,users.id AS user_id,users.username 
FROM notifications, users 
WHERE users.id = notifications.recipient_id;
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119

1 Answers1

1
SELECT 
    notifications.id,
    notifications.recipient_id,
    notifications.sender_id,
    notifications.unread,
    notifications.type,
    notifications.parameters,
    notifications.created_at,
    users1.id AS user_id_recipient,
    users1.username  AS username_recipient,
    users2.id AS user_id_sender,
    users2.username  AS username_sender
FROM notifications
INNER JOIN users AS users1 ON users1.id = notifications.recipient_id
INNER JOIN users AS users2 ON users2.id = notifications.sender_id
TopCheese
  • 220
  • 1
  • 8
  • #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 '.username_recipient, users2.id AS user_id_sender, users2.username AS us' at line 10 –  Feb 24 '17 at 22:02
  • I edited. Try it again with the content of the answer. – TopCheese Feb 24 '17 at 22:09
  • 1
    Thank you! That was the solution! –  Feb 24 '17 at 22:14