CODEWITHSUNDEEP
sql-server-2008left-join
Soph
Soph

Reputation: 2915

How to get SQL Left Join to return only one row?

Using SQL Server 2008, I am trying to do a left join between table 1 and table 2, on a column c1.

The idea is that, from the left table (TABLE 1), I grab each and every row. From the right one (TABLE 2) I only get ONE value. 

Table 1    
--------------------
document          varchar(30)

Table 2    
--------------------
idTable2          int(30)
document          varchar(30)

Sample data TABLE 1
--------------------
3846922
2000762
3064627

Sample data TABLE 2
--------------------
1            3846922
2            2000762
3            3064627
4            2000762
5            3846922 

Sample Result
--------------------
3846922     1
2000762     2
3064627     3

I used this post as a guide, but couldn't get quite to the desired result. Up till now, I get many values of the left table and many of the right one. Any ideas? The following is my sql query:

SELECT t1.document, t2.idTable2
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.document = 
(
   SELECT TOP 1 t2_aux.document
   FROM Table2 t2_aux
   WHERE t2_aux.document = t1.document
   ORDER BY t2_aux.document DESC
)

Upvotes: 1

Views: 2818

Answers (1)

AnandPhadke
AnandPhadke

Reputation: 13496

DECLARE  @t1 TABLE(document varchar(30))

INSERT INTO @t1
VALUES('3846922'),('2000762'),('3064627')

DECLARE  @t2 TABLE(idTable2 int,document varchar(30))
INSERT INTO @t2
VALUES(1,'3846922'),
(2,'2000762'),
(3,'3064627'),
(4,'2000762'),
(5,'3846922 ')

select t1.document,min(t2.idTable2) as idTable2  from @t1 t1 inner join @t2 t2
on t1.document = t2.document 
group by t1.document
order by idTable2

Upvotes: 1

Related Questions