-1

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.

Table1
sId
123X

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

What I'm trying to achieve:

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'
philipxy
  • 14,867
  • 6
  • 39
  • 83
Dave123
  • 5
  • 3
  • 1
    if both columns have the same value, then select DISTINCT. But if you truly have different values then you should adjust your sample data above to get the right answer. – Isolated Aug 19 '22 at 15:57
  • 1
    So which do you want? Could you just use `MAX`/`MIN`? The fact that you have 2 completely identical rows seems odd. – Thom A Aug 19 '22 at 15:57
  • @Larnu I fixed xId on one of the rows. i'm hoping to return just one of the rows in this example. Typically this will be a 1 to 1 relation or 1 to none, but in some cases as shown above it could be 1 to many. – Dave123 Aug 19 '22 at 16:36
  • 1
    So, considering you only want the 2 columns, use a `MIN`. – Thom A Aug 19 '22 at 16:41
  • Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Aug 19 '22 at 20:15

2 Answers2

1

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)
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • 1
    I don't understand the first line of this answer. If you find the question unclear then you have the rep to vote the question as "needing details or clarity " – Martin Smith Aug 19 '22 at 16:57
0

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.

Matthias Meid
  • 12,455
  • 7
  • 45
  • 79
  • 1
    Problem is, my current query does not have a GROUP by, and it would require it for this reason. Is there any way I can just do a LEFT JOIN that would not require a GROUP by. I guess another alternative would be to add a CTE with the data I need and then JOIN it. Ok you update it, thanks – Dave123 Aug 19 '22 at 16:51
  • I added another approach (hoping I got it right; I don't have the IDE ready right now). I guess the idea works with both a CTE or a sub-select. – Matthias Meid Aug 19 '22 at 16:53