CODEWITHSUNDEEP
mysqlsql
Teej
Teej

Reputation: 12873

Get results depending on time-based conditions

A very simple representation of my problem is:

Reviews

| mentor_id | mentee_id | status   | created    |
| 1         | 2         | active   | 2014-08-13 |
| 1         | 2         | inactive | 2014-08-20 |
| 1         | 2         | inactive | 2014-08-27 |
| 1         | 3         | inactive | 2014-08-20 |
| 1         | 2         | inactive | 2014-09-03 |

User Table

| id | first_name | last_name |
| 1  | Ivan       | Pietro    |
| 2  | Alexander  | Summers   |
| 3  | Mark       | Xavier    |

Mentorship Table

| id | mentee_id | mentor_id | created    |
| 1  | 2         | 1         | 2014-08-06 |
| 2  | 3         | 1         | 2014-08-06 |

mentor_id and mentee_id are ids of the users table.

I want to get the users.id, users.first_name and users.last_name of the mentees(user table) that have a status of inactive for the last 14 days after they were created in the mentorship table.

Upvotes: 0

Views: 71

Answers (3)

Prasanth V J
Prasanth V J

Reputation: 1196

Check this

  select user.id,user.first_name,user.last_name,max(Reviews.created) RCreated, Mentorship.created  MCreated
    from Reviews 
    inner join User on Reviews.mentee_id=USer.id 
    inner join Mentorship on Reviews.mentee_id=Mentorship.mentee_id
    where status='inactive'  
    group by User.id 
    having  DATEDIFF(RCreated, MCreated) >= 14

http://sqlfiddle.com/#!2/dfc71/26

Upvotes: 1

Raad
Raad

Reputation: 4648

Your question isn't crystal clear, but I think you are asking for:

SELECT  DISTINCT u.*
FROM    reviews r
JOIN    mentorship m
        ON m.mentee_id = r.mentee_id AND m.mentor_id = r.mentor_id
JOIN    user u
        ON u.id = r.mentee_id
WHERE   r.status =  'inactive'
AND     DATEDIFF(r.created, m.created) >= 14

This would give you:

id first_name last_name
-- ---------- ----------
2  Alexander  Summers
3  Mark       Xavier

It's difficult to know if this is what you are after because the dataset is limited (there are only 2 mentees anyway).
Hopefully it is!

Upvotes: 0

usselite
usselite

Reputation: 816

Something like this? (CURDATE() is for MySQL, you have to look into it if you use a different SQL).

SELECT users.*
FROM users, reviews
WHERE reviews.mentee_id = users.id AND reviews.status = 'inactive' AND DATEDIFF(reviews.created, CURDATE()) >= 14;

Upvotes: 0

Related Questions