CODEWITHSUNDEEP
phpmysqljoincount
Ben Longmire
Ben Longmire

Reputation: 47

MYSQL JOINs & COUNT

Have 3 Tables... Need to select users that haven't booked a lesson for the next week, and send an email.

Think I'm looking at 

WHERE `LESSONS`.`DATE` > CURDATE()  
  AND `LESSONS`.`DATE` <= (CURDATE() + INTERVAL 7 DAY)

That works to select the range of dates... However I'm then having a problem joining that to the bookings to see if they have booked, and if they HAVEN'T then I need to grab their email address... I'd rather not use PHP to loop through and query data. I obviously just want to run the single query.

As you can probably tell I'm not in the know when it comes to MYSQL.

Help appreciated. Thanks.

USERS
-----------
ID | EMAIL, ETC


LESSONS
-----------
ID | DATE, ETC


BOOKINGS
-----------
ID | LESSON_ID | USER_ID, ETC

Upvotes: 1

Views: 112

Answers (2)

Troy Jennings
Troy Jennings

Reputation: 432

use in or exists?

SELECT users.* FROM users
WHERE users.id NOT IN
(SELECT Bookings.user_id FROM Bookings JOIN Lessons ON Bookings.lesson_id = lessons.id
WHERE lessons.date > CURDATE() AND lessons.date <= (CURDATE()+INTERVAL7 7 DAY)

There may be syntactical differences in MySQL opposed to SQL server, that I'm more familiar with.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

You can try something like

SELECT  u.*
FROM    USERS u LEFT JOIN
    Bookings b  ON  u.ID = b.USER_ID LEFT JOIN
    LESSONS l   ON  b.LESSON_ID = l.ID
            AND l.DATE > CURDATE() AND l.DATE <= (CURDATE() + INTERVAL 7 DAY)
WHERE   l.ID IS NULL

This should get all users that do not have any bookings for lessons for next week.

Have a look at Introduction to JOINs – Basic of JOINs

This is a nive visual representation of joins.

Upvotes: 1

Related Questions