MySQL tricky triple join

0
votes

These are the three tables I have:
emails

    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | emailID  | int(11)      | NO   | PRI | NULL    | auto_increment |
    | subject  | varchar(100) | YES  |     | NULL    |                |
    | body     | text         | YES  |     | NULL    |                |
    | userID   | int(11)      | NO   |     | NULL    |                |
    | date     | timestamp    | YES  |     | NULL    |                |
    +----------+--------------+------+-----+---------+----------------+

people

    +----------+-------------+------+-----+---------+----------------+
    | Field    | Type        | Null | Key | Default | Extra          |
    +----------+-------------+------+-----+---------+----------------+
    | userID   | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name     | varchar(50) | YES  |     | NULL    |                |
    | email    | varchar(50) | NO   |     | NULL    |                |
    | relation | varchar(50) | YES  |     | NULL    |                |
    +----------+-------------+------+-----+---------+----------------+

toLine

    +---------+---------+------+-----+---------+-------+
    | Field   | Type    | Null | Key | Default | Extra |
    +---------+---------+------+-----+---------+-------+
    | emailID | int(11) | NO   | PRI | NULL    |       |
    | userID  | int(11) | NO   | PRI | NULL    |       |
    +---------+---------+------+-----+---------+-------+

The field emails.userID is the ID of the sender, so who the email is from.

What I need is a query that will get me emails sent by me (relation='researcher') to my course director (relation='course director'). I don't know how to do the joins so I can use the relation field in two different ways.

I've tried the query:

SELECT count(emails.emailID)
FROM emails
LEFT JOIN people ON emails.userID = people.userID
WHERE people.relation='researcher';

This gets me all the emails sent by me, but I'm having trouble figuring out how to filter this down further based on who the email is sent to.

2
mysql
It would be better if you can post some sample data and your attempts so far.. – G one

2 Answers

1
votes

Please try this query:

select distict e.* from emails e
join `toLine` t on t.`emailID` = e.`emailID`
join people pto on pto.`userID` = t.`userID`
join people pfrom on e.`userID` = pfrom.`userID`
where pfrom.relation = 'researcher' and pto.relation = 'course director'
0
votes

If you want all emails sent by a researcher to a course director, try this...

SELECT e.* FROM emails e
INNER JOIN people senders
    ON e.userID = senders.userID
WHERE senders.relation = 'researcher'
AND EXISTS (
    SELECT 1 FROM toLine t
    INNER JOIN people recipients 
        ON t.userID = recipients.userID
    WHERE e.emailID = t.emailID
    AND recipients.relation = 'course director'
)

I've used the EXISTS clause so you don't end up with duplicate emails records.