GROUP BY in a JOIN?

Question

I have a data table and a calendar table. The date table only contains the fields MonthName and FiscalYearName. My calendar table has each day for each year. How can I write the JOIN to make sure I am not getting duplicated data due to the inability to JOIN on a date? Right now am I just linking on those two fields and I am getting duplicate data.

Answer 1

Could you elaborate on the exact issue?

If month and year are the only fields, why exactly do you need to join on Calendar? To get rid of duplicates, the easiest solution might be to use a 'DISTINCT'.

SELECT DISTINCT dat.year, dat.month, *
  FROM date dat
  JOIN calendar cal
    ON dat.year  = cal.year
   AND dat.month = cal.month

Edit; Extra query to just show the MonthNum; This query uses a common table expression which gets the distinct month names and their number only once, then joins the date table on this cte.

WITH cteCalendar AS
(
SELECT DISTINCT MonthName, MonthNum FROM calendar
)
SELECT cte.MonthNum, dat.*
FROM date dat
JOIN cteCalendar cte
AS dat.MonthName = cte.MonthName