CODEWITHSUNDEEP
sqlselect
Hel
Hel

Reputation: 221

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.

Upvotes: 1

Views: 158

Answers (1)

Mureinik
Mureinik

Reputation: 311163

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)

Upvotes: 1

Related Questions