0

I am not having any luck with using a DATE() variable in the code below - getting Error in Syntax. Have tried SELECT @mydate := DATE(NOW()) as well but same error. Am using MySQL 5.5

SET @mydate = DATE(NOW())

SELECT DISTINCT(rental.id), filmCopy.location fC_id, member.id m_id, filmInfo.title fI_title,  rental.due_back,rental.returned, filmCopy.on_loan_to,reservation_date rsrvtn, reservation.id res_id
    FROM rental

INNER JOIN transactionSummary ON transactionSummary.id = rental.transactionSummary_id
INNER JOIN member ON member.id = transactionSummary.member_id
INNER JOIN filmCopy ON filmCopy.id = rental.filmCopy_id
INNER JOIN filmInfo ON filmInfo.id = filmCopy.filmInfo_id
INNER JOIN filmPriceBracket ON filmPriceBracket.filmInfo_id = filmInfo.id
AND filmPriceBracket.filmCopytype_id = filmCopy.filmCopyType_id
LEFT JOIN reservation ON reservation.filmInfo_id = filmInfo.id
AND reservation.reservation_date = @mydate 
WHERE  rental.returned IS NULL 
AND filmPriceBracket.filmCopyType_id !=24
ORDER BY  fI_folder, rsrvtn DESC, filmCopy_id, rental.due_back
southafricanrob
  • 331
  • 3
  • 15
  • You get a syntax error when running `SELECT @mydate = DATE(NOW());`? What is the complete output of the error message? – Cyclonecode Nov 20 '13 at 09:24
  • Hi, here is the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT(rental.id), filmCopy.location fC_id, member.id m_id, filmInfo.ti' at line 2 If I just run the SELECT @mydate line it runs fine but somehow is causing the main query to bomb out.. – southafricanrob Nov 21 '13 at 06:47
  • In you question you states that you have tried `SELECT @mydate := DATE(NOW()) as well but same error`. If that isn't the case remove that from your question. – Cyclonecode Nov 21 '13 at 08:47
  • So if you remove the `LEFT JOIN` clause where you actually use the `@mydate` does your query work then? – Cyclonecode Nov 21 '13 at 08:50

0 Answers0