Helena Helena - 5 months ago 8
SQL Question

Select data from tables in sql

I have these tables and I want to select my messages and my friends' messages.

CREATE TABLE Friends(
userId int,
friendId int,
CONSTRAINT PRIMARY KEY(userId, friendId)
);

CREATE TABLE Message (
id int auto_increment PRIMARY KEY,
date datetime,
text text,
urlPhoto varchar(255),
type varchar(255),
idAuthor int,
idTarget int
);


Assuming my id is 1, it's easy to select my friends' ids:

SELECT friendId FROM Friends WHERE userid=1;


but I can't use that id to select the messages.

Answer

You could use a subquery on the friends table and use the ID from there. Assuming, as in the original question, your id is 1:

SELECT *
FROM   message
WHERE  idAuthor = 1 OR
       idAuthor IN (SELECT friendId
                    FROM   friends
                    WHERE  userId = 1)