CODEWITHSUNDEEP
mysql
developer__c
developer__c

Reputation: 636

MySQL Join based on date range

I am creating a mock e-commerce database for testing a script; I want to run the following code, but also inclued a further statement to select the cards based on expiry date.

SELECT customerCard.cardNumber, customer.firstName, customer.eMailAddress, customer.lastName, cardType, expiryDate
FROM customerCard
LEFT JOIN customer
USING ( eMailAddress ) 
LEFT JOIN card
USING ( cardNumber )
WHERE card.Expiry =< DATE_FORMAT(NOW(),'%m-%Y')
ORDER BY  `customerCard`.`cardNumber` ASC

I get an error on the line with the WHERE statement - how can I fix this? What is the correct syntax?

Upvotes: 0

Views: 998

Answers (2)

developer__c
developer__c

Reputation: 636

I have figured out the correct MySQL query statement from your help above.

SELECT customerCard.cardNumber, customer.firstName, customer.eMailAddress, customer.lastName, cardType, expiryDate
FROM customerCard
LEFT JOIN customer
USING ( eMailAddress ) 
LEFT JOIN card
USING ( cardNumber ) 
WHERE DATE_FORMAT( card.expiryDate,  '%y-%m-%d' ) <= DATE_FORMAT( 
CURRENT_TIMESTAMP ,  '%y-%m-%d' ) 
ORDER BY  `customerCard`.`cardNumber` ASC 
LIMIT 0 , 30

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

Please try the following: Since no table schema/sample data provided, the code is mainly based on assumptions. such as your Expiry date is like 'mm-yyyy' format

SELECT customerCard.cardNumber, customer.firstName, 
customer.eMailAddress, customer.lastName, cardType, expiryDate
FROM customerCard CC
LEFT JOIN customer CO
ON CC.eMailAddress = CO.eMailAddress 
LEFT JOIN card CD
ON CO.cardNumber = CD.cardNumber
WHERE DATE_FORMAT(CD.Expiry,'%m-%Y') =< DATE_FORMAT(NOW(),'%m-%Y') -- put both in same format
ORDER BY  CC.cardNumber ASC

Upvotes: 1

Related Questions