CODEWITHSUNDEEP
mysqlin-subquery
MykMac
MykMac

Reputation: 11

MYSQL Inner Join & Get value from Subquery

OK, I am trying to compare two tables and then input a list from a third of names to produce a totals of values for the prior 10 days. The query runs but gives me a NULL result and only one result. If I remove the DATE_ADD and replace it with a real date from the database and put in a value for the left clause instead of using the subquery I do get what I am looking for that specific date. What I would like to do is create a list of the names with the values for that day and the last 10 days. I am sure I am not doing this right so any help is appreciated as I am fairly new to this. Simple queries are easy but putting something complex like this is new to me.

select sum(t.price) from td.trs as t
inner join td.order as o on o.trsid=t.id
inner join pts.product as p on p.id=o.grp_id
where t.invoice_date=DATE_ADD(CURRENT_DATE(),INTERVAL 10 DAY)
and left(t.mfgid,3) IN (select name from name.list);

Upvotes: 1

Views: 364

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

You are probably not getting any results because you are doing DATE_ADD to the CURRENT_DATE(), which will give you a date 10 days in the future. If you want to get all items for last 10 days, use

WHERE t.invoice_date BETWEEN CURRENT_DATE() AND DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)

Upvotes: 0

juergen d
juergen d

Reputation: 204756

change

where t.invoice_date=DATE_ADD(CURRENT_DATE(),INTERVAL 10 DAY)

to

where t.invoice_date >= DATE_ADD(CURRENT_DATE(),INTERVAL -10 DAY)

Upvotes: 1

Related Questions