General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>First of all, what is "dailylog"? You don't have this table in FROM clause.
It holds the working date, and information related to that date. In this case it's a constant value because i only want the time/sale/cxl info for one day.
>Second, try do not SELECT fields that you don't have in GROUP BY. Fox allows it, but standard SQL does not (I mean "sh.pub as pub") and its value very much questionable.
I'm not exactly sure what you mean. This program isn't likely to leave native VFP, so the standard SQL isn't a serious issue. (thanks for the warning though) I understand it's questionable because if there is no sale record, there will be no value. In this case, if there is no sale then there is no pub either.
>And the most important thing: your WHERE restricts results so you will not have reps without sales for this date (sh.date==dailylog.date).
If you want keep them, move conditions from WHERE to JOIN ON.
FROM timehist th left join salehist sh on th.repid==sh.repid ;
__AND sh.date==dailylog.date
Thanks very much for noticing this. I'd have gone crazy trying to find that later. So the Where is restricting AFTER the tables are joined?
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only