SQL Query with conditional JOIN

The scenario:

Table1

CatId|Name|Description

Table2

ItId|Title|Date|CatId (foreign key)

I want to return all rows from Table1 and Title,Date from Table2, where The returned from Table 2 must be the Latest one by the date column. (in second table there many items with same CatId and I need just the latest)

I have 2 queries but can't merge them together:

Q开发者_运维知识库uery 1:  
SELECT Table1.Name,  Table1.Description,
       Table2.Title, Table2.Date
FROM 
       Table1 LEFT JOIN Table2 ON Table1.CatId=Table2.CatId

Query2:

SELECT TOP 1 Table2.Title, Table2.Date
FROM 
    Table2
WHERE 
    Table2.CatId = @inputParam
ORDER BY Table2.Date DESC

---

You can use a UNION, but you'll need to make the columns match up:

OK, after rereading the question, I understand what you're trying to do.

This should do the trick:

SELECT Table1.Name,  Table1.Description,
 T2.Title, T2.Date

FROM
 Table1

LEFT JOIN (
 SELECT CatId, Title, Date, ROW_NUMBER() over (ORDER BY CatId, Date DESC) - RANK() over (ORDER BY CatID) as Num

 FROM  Table2) T2 on T2.CatId = Table1.CatId AND T2.Num = 0

---

Sounds like you're talking about a groupwise maximum (newest row in Table2 for each matching row in Table1), in which case, the easiest way is use ROW_NUMBER:

WITH CTE AS
(
    SELECT
        t1.Name, t1.Description, t2.Title, t2.Date,
        ROW_NUMBER() OVER (PARTITION BY t1.CatId ORDER BY t2.Date DESC) AS Seq
    FROM Table1 t1
    LEFT JOIN Table2 t2
        ON t2.CatId = t1.CatId
)
SELECT *
FROM CTE
WHERE Seq = 1
OR Date IS NULL

---

Shouldn't this work?

SELECT Table1.Name,  Table1.Description,
   T2.Title, T2.Date
FROM 
   Table1 LEFT JOIN (

SELECT TOP 1 Table2.CatId Table2.Title, Table2.Date
FROM 
Table2
WHERE 
Table2.CatId = Table1.catId 
ORDER BY Table2.Date DESC

) T2 
ON Table1.CatId=T2.CatId