CODEWITHSUNDEEP
sql
NorCalKnockOut
NorCalKnockOut

Reputation: 878

SQL Query for all purchases in a given month

I'm going through a SQL tutorial, and came across this question. I've been stuck for sometime.

Customers

id INTEGER PRIMARY KEY    
lastname VARCHAR    
firstname VARCHAR

Purchases

id INTEGER PRIMARY KEY    
customers_id INTEGER FOREIGN KEY customers(id)    
purchasedate DATETIME    
purchaseamount REAL

Write a statement that gives a list of all customers who purchases something this month.

I know I want to inner-join the tables on customer_id and then get the customer names where the month is February, does this look right?

SELECT * from Purchases
    inner join Customers
    on Purchases.customers_id=Customers.id
WHERE MONTH(purchasedate) = 2

Upvotes: 1

Views: 1639

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Your query will return a list with duplicates name. Just add DISTINCT

Dont use *, just include the fields your require.

Also include the id, just in case two ppl has same first and last name

SELECT DISTINCT Customers.id, Customers.LastName, Customers.firstname 
from Purchases
inner join Customers
  on Purchases.customers_id = Customers.id
WHERE MONTH(purchasedate) = 2

In case you want only the current month sales. you need specify your RDBMS because date function are different.

For example in postgres sql you can use Get first date of month in postgres

SELECT DISTINCT Customers.id, Customers.LastName, Customers.firstname 
from Purchases
inner join Customers
  on Purchases.customers_id = Customers.id
WHERE date_trunc('month', current_date) = date_trunc('month', purchasedate)

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

I will go with EXISTS which will avoid duplicate 

SELECT Customers.id,
       Customers.LastName,
       Customers.firstname
FROM   Customers
WHERE  EXISTS (SELECT 1
               FROM   Purchases
               WHERE  Purchases.customers_id = Customers.id
                      AND Month(purchasedate) = 2)

Considering you want to pull customer information who purchased in 2 month of any year

Upvotes: 2

Carsten Massmann
Carsten Massmann

Reputation: 28196

Yeah, or to avoid the whole distinct business you could write

SELECT id, LastName, firstname FROM Customers
WHERE EXISTS ( SELECT 1 FROM Purchases 
               WHERE customers_id=Customers.id
               AND MONTH(purchasedate)=2 AND YEAR(purchasedate)=2016 )

Upvotes: 3

Related Questions