SQL query, LEFT JOIN but only get one value-CodePudding

I have a slightly unusual scenario.

Basically I'm trying to do a LEFT JOIN to a table, that can contain multiple values that meet my criteria, but I only want to select one of them, here are what my tables look like:

Table1
sId
123X


Table2
sId       Type     EDate         xId
123x      XX       8/19/2022     144
123x      XX       8/19/2022     145

So what I'm trying to achieve is:

sId     xId
123x    144

Select t1.sId, t2.xId
FROM table1 t1 
LEFT JOIN table2 t2 on t1.sId = t2.sId and t2.Type='XX'

CodePudding user response：

Another situation where N ways are possible and no clear definition from poster :(

Select t1.sId, tx.xId
FROM table1 t1 
outer apply (select top(1) xId 
             from table2 t2 
             where t1.sId = t2.sId and t2.Type='XX'
             order by eDate, xid --????
) tx(xId)

CodePudding user response：

If you only need the ID you can use a GROUP BY and MIN, as pointed out in the comments:

SELECT t1.sId, MIN(t2.xId)
FROM table1 t1 
LEFT JOIN table2 t2 ON t1.sId = t2.sId AND t2.Type = 'XX'
GROUP BY t1.sId

In case you want more than that, i.e. not just the maximum ID, but the whole row, I recommend this:

SELECT t1.sId, q.xId
FROM table1 t1 
LEFT JOIN (
    SELECT t2.*, RowNumber = ROW_NUMBER () OVER (PARTITION BY t2.sId ORDER BY t2.xId)
    FROM table2 t2
) q ON t1.sId = q.sId AND q.Type = 'XX' AND RowNumber = 1

In a nutshell ROW_NUMBER is a windowing function, which lets you compute a row number over a certain partition of records in a specified order. Then, you can join that, by matching on RowNumber = 1.

You can use a CTE too instead of a sub-select. Same idea with the ROW_NUMBER function should work.