Join table data.

Hi,

anyone can help or guide me how to solve this issue.

i have 2 set of data(or table) to join.

1) is weekday (data will be from sunday, monday, ...., and saturday)

2) is a set of record that produce from monday to friday(this is not direct table, need to use join table to join master and detail table group it and do a count() function then only i can get the data set).

now i want to join both table to show the result of everyday. but let say Tuesday is public holiday. so there is no result on that. but my result set must still show from monday to friday, and every day also need a result. if no record then it should assign a 0(zero) value.

example of the data set

mon 100 record, wed 78 record, thu 54 record, fri 67 record.

then my result set should like the below.

mon 100, tue 0, wed 78 record, thu 54 record, fri 67 record

Hi Terence,

So if your two tables were called t_WeekDay (containing Monday - Sunday) and t_WeekDayData (containing data for Monday etc), then your query would be something like:

SELECT wd.Day,
ISNULL(COUNT(wdd.Data), 0) as 'Count Of Data'
FROM t_WeekDay wd
LEFT JOIN t_WeekDayData wdd ON wd.WeekDayID = wdd.WeekDayID
GROUP BY wd.Day
ORDER BY wd.Day ASC

Cheers,

Rob