MYSQL Inner Join & Get value from Subquery

Question

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);

Why is there "Left Join " in the title? There is no `LEFT JOIN` in the query. — ypercubeᵀᴹ, Nov 26 '12 at 20:25

score 1 · Answer 1 · answered Nov 26 '12 at 20:25

1

change

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

to

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

answered Nov 26 '12 at 20:25

juergen d

201,996
37
293
362

That did the trick in the summing up the totals! Thank you so much! Would it be possible to have it break it out by name and name though or I am reaching for too much? Ideally, it would show me the names from the IN clause with a breakdown for that name and day but guessing on my code I am no where near where I need to be. – MykMac Nov 26 '12 at 20:33
I am not sure what you try to achive. It would be best to ask a new question (since this is beyond the current question) and explain in detail what you want. Please add relevant table stucture and sample data to your new question. And if you really want fast answers provide a [SQLFiddle](http://sqlfiddle.com) example as well. – juergen d Nov 26 '12 at 20:40
I certainly can and I appreciate it! I'll go ahead and post another question. Cheers! – MykMac Nov 26 '12 at 20:42

score 0 · Answer 2 · answered Nov 26 '12 at 20:28

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)

MYSQL Inner Join & Get value from Subquery

2 Answers2