JOIN question

[JontyMC]

I have two tables I want to join, but I only want the top row of the second table, ie:

table1:
col1 col2
aaaa bbbb
cccc dddd

table2:
col3 col4
aaaa eeee
aaaa ffff

Desired result:
col1 col2 col4
aaaa bbbb eeee
cccc dddd null

At the moment I have

SELECT * FROM table1 t1
LEFT JOIN table2 t2
ON t1.col1 = t2.col3

But I get:
col1 col2 col4
aaaa bbbb eeee
aaaa bbbb ffff
cccc dddd null

How can I adjust the query?

Jon

"I don't regret this, but I both rue and lament it.

 

[SQLDenis]

create table #table1 (col1 varchar(10), col2 varchar(10))
insert into #table1
values ('aaaa','bbbb')
insert into #table1
values ('cccc','dddd')


create table #table2 (col3 varchar(10), col4 varchar(10))
insert into #table2
values ('aaaa','eeee')
insert into #table2
values ('aaaa','ffff')


select col1,col2,col4 from #table1 t1 left join(
select col3,min(col4) col4 from #table2
group by col3) t2
ON t1.col1 = t2.col3

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[JontyMC]

In reality, my table 2 has several columns. I can't use min() on each column, because I that might get data from different rows.

Jon

"I don't regret this, but I both rue and lament it.

 

[JontyMC]

I worked it out myself:

SELECT * FROM table1 t1
LEFT JOIN table2 t2
ON t1.col1 = t2.col3
WHERE t2.col4 = (SELECT MIN(col4) FROM table2 WHERE col3 = t2.col3)

Jon

"I don't regret this, but I both rue and lament it.