In article <[EMAIL PROTECTED]>, Partap Davis <[EMAIL PROTECTED]> writes:
> I'm graphing the data from this query using dates on the x axis. The > input to my graph module (GD::Graph) requires a constant-length list. > So if any days in my selection range have no data, I need to fill the > space with an empy value. > For example, say my date range is '2004-10-01' to '2004-10-05' > and the query returns: > day, amount > 2004-10-01, 50 > 2004-10-02, 100 > 2004-10-04, 250 > I have to do some date manipulation in perl afterward to check for > missing values... > If I could get a query that returned: > day, amount > 2004-10-01, 50 > 2004-10-02, 100 > 2004-10-03, NULL > 2004-10-04, 250 > 2004-10-05, NULL > That would be so much nicer in some cases. I'm thinking it would be > sort of like the output from a LEFT JOIN if I had a table containing > just a bunch of sequential dates...but I don't...and the dates can > actually be arbitrary, so it would have to be a pretty big table, with > no real data in it. I guess you'd need indeed a helper table, but it it doesn't need to be big. For the example above, you could do: CREATE TABLE helper (x INT); INSERT INTO helper VALUES (0), (1), (2), (3), (4); SELECT '2004-10-01' + INTERVAL x DAY, ... FROM helper LEFT JOIN yourtbl ON day = '2004-10-01' + INTERVAL x DAY ...; Do you see the picture? Your helper table needs as many rows as you want to return from your query, not more. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
