6

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?

Community
  • 1
  • 1
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191

4 Answers4

4

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

void
  • 7,760
  • 3
  • 25
  • 43
  • Nop, it does not produce desired results. Check it yourself. – Przemyslaw Remin Apr 03 '15 at 09:14
  • I just seen the pictures in question, let me to review it again – void Apr 03 '15 at 09:15
  • @PrzemyslawRemin please check it again how do you say it doesn't gives correct result, there was a missed comma which is corrected please check it again and give a feedback – void Apr 03 '15 at 09:21
  • you're welcome, also there is some points that you have to notice if you want to use a simple subquery, I actually wanted to write it but because I the points I decided to write this version. – void Apr 03 '15 at 09:52
3
select table1.id, 
(SELECT TOP 1 category FROM table2 WHERE table2.id=table1.id ORDER BY category ASC) AS category
FROM table1
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
VincentPzc
  • 31
  • 3
1
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
Shirishkumar Bari
  • 2,692
  • 1
  • 28
  • 36
  • Goal achieved with this method, too. Thanks. – Przemyslaw Remin Apr 03 '15 at 10:55
  • a cryptic solution with no guarantee of getting the category with the lowest ID. If there are duplicate categories in table 2, this may return a different number of rows for some categories – t-clausen.dk Apr 03 '15 at 11:04
  • joining on a varchar when you have an integer available is not good practice either – t-clausen.dk Apr 03 '15 at 11:44
  • I have joined on integer only . the category check is in the Where clause. Wouldn't it be too much to worry about the data which we don't know at this point (Speculative generality) . There can be a lot of different data condition we can think of . but I think this satisfies the problem statement. – Shirishkumar Bari Apr 03 '15 at 11:50
  • you are getting several different categories and comparing them in a where clause. That is a join. It is certainly possible to make a join in the where clause. And yes, you should worry about data you don't know. Writing the script correct for future data should always be a priority – t-clausen.dk Apr 03 '15 at 11:53
1

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

change CROSS APPLY to OUTER APPLY

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191