MySQL Forums
Forum List  »  Perl

Re: Perl Help, Integrating two tables.
Posted by: Bill Karwin
Date: August 08, 2006 06:43PM

A few corrections:

1. There is no "==" operator in SQL. The equality comparison operator is simply "=".

2. Don't use correlation names in front of functions. Only columns get the correlation names.

3. A WHERE clause always goes before the GROUP BY clause. But I've moved the join condition into SQL92 syntax, because we need an outer join. See point #7 below.

4. Don't bother to format a date just to use it in COUNT().

5. When you group by week, there are up to 7 distinct date values in that week, and potentially multiple rows from NUM_JOBS per group. Which value of START_DT do you want to use for the column? MySQL will choose one of the rows in the group from which to use the value, but it won't necessarily be the one you want. When using GROUP BY, it's recommended not to include columns that contain multiple distinct values.

6. I do not recommend using the 2-digit year value. I always use the 4-digit year, just to be clear. We don't want another Y2K crisis.

7. If there are no values in NUM_JOBS for a whole week, but you want a row in the query result for that week with a COUNT() of zero, you need to use a LEFT OUTER JOIN.

SELECT 
  C.WEEK AS WEEKS,
  C.FORMAT_DATE,
  COUNT(N.START_DT) AS VAL
FROM CALENDAR AS C
  LEFT OUTER JOIN NUM_JOBS AS N 
    ON DATE_FORMAT(N.START_DT, '%Y-%m-%d') = C.FORMAT_DATE
GROUP BY WEEKS;



Edited 1 time(s). Last edit at 08/20/2006 01:05PM by Bill Karwin.

Options: ReplyQuote


Subject
Written By
Posted
Re: Perl Help, Integrating two tables.
August 08, 2006 06:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.