SQL LEFT JOIN first row only

Question

Let's assume we have such data set:

Table: DataTable1

ID      ExperienceId   LanguageId    ...
-------------------------------------------
1       1              1
2       1              2
3       1              3
4       2              1
5       2              2
6       2              3
7       3              1
8       3              2
9       3              3
...

Table: DataTable2

ID      SomeId OtherId LanguageId    ...
-------------------------------------------
1       459    1       1
2       459    1       2
3       459    1       3
4       245    2       1
5       245    2       2
6       245    2       3
7       295    3       1
8       295    3       2
9       295    3       3
...

I want to join those tables and get only SomeId column ignoring the LanguageId column. To make it clearer:

SELECT
    t2.SomeId AS RequiredId
    -- ...other data mainly from t2
FROM DataTable1 AS t1
LEFT JOIN DataTable2 AS t2 
    ON t2.OtherId = t1.ExperienceId 
    AND t2.LanguageId = 
        (SELECT TOP 1 t1.LanguageId
         ORDER BY t1.LanguageId)

This query should return (if it wasn't wrong, clearly) rows:

SomeId    ...
----------------
459       ...
245       ...
295       ...
...

Now it returns three times of identical data (with only LanguageId different).

I would try to filter it with WHERE t1.LanguageId = 1 if I was sure it always exists, but I'm not sure. Rows can be with LanguageId from 1 to 3, also they can be only with ID 2, etc. Rows surely will have at least one LanguageId.

Now my question is: how can I join tables with unique values with one column completely ignored?

Try using `group by LanguageId` – Jay Kazama Jul 17 '14 at 07:52 — Jay Kazama, Jul 17 '14 at 07:52

score 2 · Accepted Answer · answered Jul 17 '14 at 07:54

Wrapping it in another query does the trick?

SELECT RequiredId, <all_the_other_fields> from (
SELECT t2.SomeId AS RequiredId
-- ...other data mainly from t2
FROM DataTable1 AS t1
   LEFT JOIN DataTable2 AS t2 
   ON t2.OtherId = t1.ExperienceId 
   AND t2.LanguageId = 
      (SELECT TOP 1 t1.LanguageId
      ORDER BY t1.LanguageId)
   ) group by RequiredId, <all_the_other_fields>

or even not extracting the column in the first place?

SELECT distinct t2.SomeId AS RequiredId
-- ...other data mainly from t2 BUT not the Language id
FROM DataTable1 AS t1
   LEFT JOIN DataTable2 AS t2 
   ON t2.OtherId = t1.ExperienceId 
   AND t2.LanguageId = 
      (SELECT TOP 1 t1.LanguageId
      ORDER BY t1.LanguageId)

Thank you, first approach works just fine :) Had problems with EF until I saw that my GROUP BY clause was inside inner query, not outside of it. Changed that and now it works. — YOhan, Jul 17 '14 at 12:45

score 1 · Answer 2 · answered Jul 17 '14 at 07:56

1

Try this:

;with cte as 
(select *, row_number() over (partition by someid order by languageid) rn
 from datatable2)

select * 
from datatable1 dt
left join cte c on dt.experienceid = c.otherid and c.rn = 1

answered Jul 17 '14 at 07:56

shree.pat18

21,449
3
43
63

score 1 · Answer 3 · answered Jul 17 '14 at 08:01

For such things when you need to select top in the subquery CROSS APPLY OR 'OUTER APPLY' is very handy

t2.SomeId AS RequiredId
    -- ...other data mainly from t2
FROM DataTable1 AS t1
OUTER APPLY ( SELECT TOP 1 t1.LanguageId
               FROM DataTable2 
               WHERE DataTable2 .OtherId = t1.ExperienceId 
                AND  t2.LanguageId = t1.LanguageId
             ORDER BY t1.LanguageId
             ) AS t2

score 0 · Answer 4 · answered Jul 17 '14 at 09:21

0

Try this:

SELECT  DISTINCT t2.SomeId AS RequiredId
        -- ...other data mainly from t2
FROM    DataTable1 AS t1
        LEFT JOIN DataTable2 AS t2 
                  ON t2.OtherId = t1.ExperienceId 
WHERE   t2.LanguageId = t1.LanguageId

answered Jul 17 '14 at 09:21

Jesuraja

3,774
4
24
48

score 0 · Answer 5 · answered Jul 17 '14 at 10:18

Are you looking for this (Fiddle: http://sqlfiddle.com/#!3/811b8/12)?:

SELECT    dt2.*
FROM      DataTable1 dt1 INNER JOIN DataTable2 dt2
          ON dt1.ExperienceID = dt2.OtherID AND
          dt1.LanguageID = dt2.LanguageID
WHERE     dt2.LanguageID = (SELECT MIN(LanguageID) FROM DataTable1);

produces:

ID  SOMEID  OTHERID     LANGUAGEID
1   459     1           1
4   245     2           1
7   295     3           1

SQL LEFT JOIN first row only

5 Answers5

Linked