join Tables for Calendar Query, show all records from reference Table.

Hi experts,
I have following query. And in my solution i want to see each day from for example the month july, even if i have no values entered in tblMAAbsenzen. But, unfortunately I do not get the dates if I have another ma_id, with values in tblMAAbsenzen. I want to make this query for a better view.
Thanks in advance for your help.
lanter007

SELECT     refCalendar.Datum, tblMAAbsenzen.ma_id, tblMAAbsenzen.absenz_id AS ferien, tblMAAbsenzen.absenz_id AS krankheit,
                      tblMAAbsenzen.absenz_id AS unfall, tblMAAbsenzen.absenz_id AS militaer, tblMAAbsenzen.absenz_id AS mutterschaft,
                      tblMAAbsenzen.absenz_id AS kompensation, tblMAAbsenzen.absenz_id AS umzug, tblMAAbsenzen.absenz_id AS todesfall,
                      tblMAAbsenzen.absenz_id AS heirat, tblMAAbsenzen.absenz_id AS feiertag, tblMAAbsenzen.absenz_id AS pflege, tblMAAbsenzen.dauer_in_tagen,
                      MONTH(refCalendar.Datum) AS Monat, YEAR(refCalendar.Datum) AS Jahr
FROM         refCalendar LEFT OUTER JOIN
                      tblMAAbsenzen ON refCalendar.Datum = tblMAAbsenzen.datum
WHERE     (MONTH(refCalendar.Datum) >= 7) AND (MONTH(refCalendar.Datum) <= 8) AND (YEAR(refCalendar.Datum) = 2006) AND (tblMAAbsenzen.ma_id = 11 OR
                      tblMAAbsenzen.ma_id IS NULL)
ORDER BY refCalendar.Datum
lanter007Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
imran_fastConnect With a Mentor Commented:
try this
====

SELECT     refCalendar.Datum, tblMAAbsenzen.ma_id, tblMAAbsenzen.absenz_id AS ferien, tblMAAbsenzen.absenz_id AS krankheit,
                      tblMAAbsenzen.absenz_id AS unfall, tblMAAbsenzen.absenz_id AS militaer, tblMAAbsenzen.absenz_id AS mutterschaft,
                      tblMAAbsenzen.absenz_id AS kompensation, tblMAAbsenzen.absenz_id AS umzug, tblMAAbsenzen.absenz_id AS todesfall,
                      tblMAAbsenzen.absenz_id AS heirat, tblMAAbsenzen.absenz_id AS feiertag, tblMAAbsenzen.absenz_id AS pflege, tblMAAbsenzen.dauer_in_tagen,
                      MONTH(refCalendar.Datum) AS Monat, YEAR(refCalendar.Datum) AS Jahr
FROM         refCalendar
      LEFT OUTER JOIN
                      tblMAAbsenzen ON refCalendar.Datum = tblMAAbsenzen.datum
                  AND (tblMAAbsenzen.ma_id = 11 OR
                            tblMAAbsenzen.ma_id IS NULL)
WHERE     (MONTH(refCalendar.Datum) >= 7) AND (MONTH(refCalendar.Datum) <= 8) AND (YEAR(refCalendar.Datum) = 2006)
            
ORDER BY refCalendar.Datum
0
 
RickBeebeCommented:
If all the dates for July exist in refCalendar - all the rows will display with nulls for all columns except for refCalendar.Datum where they don't exist on tblMAAbsenzen.  If they do exist on tblMAAbsenzen the ma_id is 11 or null the entire row will show.
0
 
lanter007Author Commented:
perfect!!!!!!!!!
thank you very much.
0
 
RickBeebeCommented:
So, this is not a required busines rule?

AND (tblMAAbsenzen.ma_id = 11 OR
                      tblMAAbsenzen.ma_id IS NULL)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.