CODEWITHSUNDEEP
sqlsql-serverleft-join
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

How to left join to first row in SQL Server

How to left join two tables, selecting from second table only the first row? first match

My question is a follow up of: SQL Server: How to Join to first row I used the query suggested in that thread.

CREATE TABLE table1(
  id INT NOT NULL
);
INSERT INTO table1(id) VALUES (1);
INSERT INTO table1(id) VALUES (2);
INSERT INTO table1(id) VALUES (3);
GO

CREATE TABLE table2(
  id INT NOT NULL
, category VARCHAR(1)
);
INSERT INTO table2(id,category) VALUES (1,'A');
INSERT INTO table2(id,category) VALUES (1,'B');
INSERT INTO table2(id,category) VALUES (1,'C');
INSERT INTO table2(id,category) VALUES (3,'X');
INSERT INTO table2(id,category) VALUES (3,'Y');
GO

------------------
SELECT 
table1.* 
,FirstMatch.category
FROM table1

CROSS APPLY (
    SELECT TOP 1 
    table2.id
    ,table2.category   
    FROM table2 
    WHERE table1.id = table2.id
    ORDER BY id
    )
    AS FirstMatch

However, with this query, I get inner join results. I want to get left join results. The tabel1.id in desired results should have '2' with NULL. How to do it?

Upvotes: 6

Views: 1229

Answers (4)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

Following the comment of t-clausen.dk this does the job:

change CROSS APPLY to OUTER APPLY

Upvotes: 1

VincentPzc
VincentPzc

Reputation: 31

select table1.id, 
(SELECT TOP 1 category FROM table2 WHERE table2.id=table1.id ORDER BY category ASC) AS category
FROM table1

Upvotes: 3

Shirish Bari
Shirish Bari

Reputation: 2722

SELECT    table1.id ,table2.category 
FROM table1 Left join table2
on table1.id = table2.id
where table2.category = ( select top 1 category  from table2 t where table1.id = t.id) 
OR table2.category is NULL

Upvotes: 1

void
void

Reputation: 7890

use row_number and left join

with cte as(

select id,
       category,
       row_number() over(partition by id order by category) rn
       from table2
)
select t.id, cte.category
from table1 t
left outer join cte 
on t.id=cte.id and cte.rn=1

OUTPUT:

id  category
1   A
2   (null)
3   X

SQLFIDDLE DEMO

Upvotes: 4

Related Questions