Joining tables

Can someone please advise how i join the following two tables:
I want to left join on dt,Category and subcategory from Table 1
Table 1:
SELECT TOP 100 PERCENT Y, M, Category, SubCategory,dt
FROM (SELECT dbo.tblCalendar_jp.dt, dbo.tblCalendar_jp.Y,
dbo.tblCalendar_jp.M, dbo.TblRetrospectiveDiary.SubCategory,
dbo.TblRetrospectiveDiary.Category
FROM dbo.tblCalendar_jp CROSS JOIN
dbo.TblRetrospectiveDiary
WHERE (dbo.tblCalendar_jp.FY = 2005)) calrd
GROUP BY Y, M, Category, SubCategory
ORDER BY Y, M
Table 2:
SELECT Category, SubCategory, dt, [Time]
FROM (SELECT Category, SubCategory, LEFT(DateFrom, 11) AS dt,
DateTo, DATEDIFF(mi, DateFrom, DateTo) AS [Time]
FROM dbo.TblRetrospectiveDiary) rd
Thanks in anticipationjust a hint
SELECT TOP 100 PERCENT Y, M, Category, SubCategory,dt
FROM (SELECT dbo.tblCalendar_jp.dt, dbo.tblCalendar_jp.Y,
dbo.tblCalendar_jp.M, dbo.TblRetrospectiveDiary.SubCategory,
dbo.TblRetrospectiveDiary.Category
FROM dbo.tblCalendar_jp CROSS JOIN
dbo.TblRetrospectiveDiary
-->> stop here
join -- this is the join
(
SELECT Category, SubCategory, dt, [Time]
FROM (SELECT Category, SubCategory, LEFT(DateFrom, 11) AS dt,
DateTo, DATEDIFF(mi, DateFrom, DateTo) AS [Time]
FROM dbo.TblRetrospectiveDiary) rd
) as a
on
-- join statement here use "a" as alias of 2nd query
--continueing statement from Q1
WHERE (dbo.tblCalendar_jp.FY = 2005)) calrd
GROUP BY Y, M, Category, SubCategory --include the other fields in the
select list
ORDER BY Y, M
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Keith Rudd" wrote:

> Can someone please advise how i join the following two tables:
> I want to left join on dt,Category and subcategory from Table 1
> Table 1:
> SELECT TOP 100 PERCENT Y, M, Category, SubCategory,dt
> FROM (SELECT dbo.tblCalendar_jp.dt, dbo.tblCalendar_jp.Y,
> dbo.tblCalendar_jp.M, dbo.TblRetrospectiveDiary.SubCategory,
> dbo.TblRetrospectiveDiary.Ca
tegory
> FROM dbo.tblCalendar_jp CROSS JOIN
> dbo.TblRetrospectiveDiary
> WHERE (dbo.tblCalendar_jp.FY = 2005)) calrd
> GROUP BY Y, M, Category, SubCategory
> ORDER BY Y, M
>
> Table 2:
> SELECT Category, SubCategory, dt, [Time]
> FROM (SELECT Category, SubCategory, LEFT(DateFrom, 11) AS dt,
> DateTo, DATEDIFF(mi, DateFrom, DateTo) AS [Time]
> FROM dbo.TblRetrospectiveDiary) rd
> Thanks in anticipation
>
>