Search code examples
mysqlsqldatetimesubquerydate-arithmetic

Writing a sub-query using a date clause

So basically I'm trying to write something that prints the customer's name and area code if they have made a booking within the last 6 months of the current date.

My code looks like this (it has to be a subquery, not using join)

SELECT custfirstname, custareacode
FROM Customer
WHERE customerid IN (SELECT bookingdate
                FROM Bookings
                  WHERE DateDiff(CURDATE(), bookingdate) <=180);

Yet it doesn't work, but I can get it to work using a join function, but I can't with subquery, how would I do it in a subquery without a join function?

Solution

  • You need to select customerid from bookings table

    SELECT custfirstname, custareacode
FROM Customer
WHERE customerid IN (SELECT customerid
                FROM Bookings
                  WHERE DateDiff(CURDATE(), bookingdate) <=180);