SQL Question

[tobeyt23]

I have the below 2 tables and want to know if I can do 1 query to pull the data i need. In the freebie table I have a all the items that will be allowed to be displayed based on the from_date - to_date as compared to todays date. The freebie_customers will hold the info it the customer has decided to accept or decline said freebie. So what I would like to pull is only the freebies that meet the date requirements and have not been decided on by the customer currently looking at them. Any suggestions?

 

freebie:
  freebie_id
  title
  filename
  description
  status
  from_date (date)
  to_date (date)
  created_time (datetime)
  update_time (datetime)

freebie_customer:
  id
  freebie_id
  customer_id
  status
  added (datetime)

 

[Mark1inLA]

I'll take first crack at it. based on these assumptions:

• freebie.status represents whether or not the freebie is available
  
• freebie_customer.status is the customer's decision of whether they accept or not
  
• a record will only exist in freebie_customer if it's been decided
  

 

here's the query i came with:

SELECT
freebie.freebie_id,
freebie.title,
freebie.filename,
freebie.description,
freebie.status,
freebie.from_date,
freebie.to_date,
freebie.created_time,
freebie.update_time
FROM 
freebie LEFT JOIN freebie_customer 
ON freebie.freebie_id = freebie_customer.id 
WHERE
freebie_customer.id IS NULL AND
freebie.from_date >= [start_date] AND
freebie.to_date <= [end_date] AND
freebie.status = [available] AND
freebie_customer.customer_id = [customer_id]

 

 

 

[tobeyt23]

Nope that doesn't work

[kickstart]

Hi

 

The solution Mark1inLA has put up looks almost there, except for the extra "AND freebie_customer.customer_id = [customer_id]" at the end. Problem comes when there is a match but which is then exluded by the WHERE clause (ie, say there was a match between the 2 tables but the status wasn't available).

 

Minor changes to fix it:-

 

SELECT freebie.freebie_id, freebie.title, freebie.filename, freebie.description, freebie.status, freebie.from_date, freebie.to_date, freebie.created_time, freebie.update_time
FROM freebie 
LEFT OUTER JOIN freebie_customer 
ON freebie.freebie_id = freebie_customer.id 
AND freebie.status = [available]
AND NOW() BETWEEN freebie.from_date AND freebie.to_date 
WHERE freebie_customer.id IS NULL