How can I create a View from multiple rows from multiple tables?

Question

I'm building a database for a chat application and I'm having a spot of trouble building a View to combine the Chats table and the Users table.

The Chats table has fields sender_id, receiver_id which map to the user_id field in the Users table. Now, I want to create a View which will combine the Chats with the Users table, and also provide the sender's and receiver's profile picture(in the fields sender_pic and receiver_pic). These can be fetched from the profile_pic field from the Users table.

What's the SQL syntax for doing this?

user275683 · Accepted Answer · 2014-04-05 17:25:59Z

1

Your syntax will be as follows, because you need both sender and receiver you need to join to users table twice.

CREATE VIEW SomeFancyName
AS
    SELECT s.profile_pic AS sender_pic
           ,r.profile_pic AS receiver_pic
        FROM Chats c
        JOIN users s
            ON c.sender_id = s.user_id
        JOIN users r
            ON c.receiver_id = s.user_id

now you can add columns that you need from each

answered Apr 5, 2014 at 17:25

user275683

Sign up to request clarification or add additional context in comments.

Comments

VMai · Accepted Answer · 2014-04-05 17:25:39Z

0

You've got to use alias names:

SELECT
    s.profile_pic AS sender_pic,
    r.profile_pic AS receiver_pic
FROM
    Chats
INNER JOIN
    Users AS s      -- s will represent the sender
ON
    Chats.sender_id = s.user_id
INNER JOIN
    Users AS r      -- and r the receiver
ON
    Chats.receiver_id = r.user_id;

answered Apr 5, 2014 at 17:25

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Comments

nicopico · Accepted Answer · 2014-04-05 17:27:30Z

0

You need to JOIN the Chats table with the Users table.

As you have 2 joins to the same table (one for the receiver, one for the sender), you'll also need to use table alias to differentiate them:

SELECT Chats.sender_id, Sender.profile_pic as Sender_profile_pic, 
       Chats.receiver_id, Receiver.profile_pic as Receiver_profile_pic, ...
  FROM Chats
  JOIN Users Receiver ON Users.user_id = Chats.receiver_id
  JOIN Users Sender ON Users.user_id = Chats.sender_id

answered Apr 5, 2014 at 17:27

nicopico

3,6361 gold badge30 silver badges30 bronze badges

Collectives™ on Stack Overflow

How can I create a View from multiple rows from multiple tables?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related