SQL select with join command help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL select with join command help

Author

Topic

Razzle00
Starting Member

35 Posts

Posted - 2007-04-03 : 14:38:16

Hi,

How could I pull all the records from 1 table and only the record with the minimum value from the second table where the 2 tables id fields match?

table 1 looks like this....
id customer
1 abc company
2 xyz company
3 pgj company

table 2 looks like
id salesdate
1 01/01/2005
1 02/03/2006
1 03/05/2007
2 01/01/2004
3 12/31/2006
3 04/04/2003

select c.id,c.customer,min(s.salesdate) from customer c inner join sales s on s.id=c.id

result needs to be 1 of each customer record from the first table and only the minimum value record for the salesdate of the sales table.

1 abc company 01/01/2005
2 xyz company 01/01/2004
3 pgj company 04/04/2003

Thanks,

Razzle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 14:46:01

select t1.id, t1.customer, t2.msd as MinSalesDate
from table1 as t1
inner join (select id, min(salesdate) as msd from table2 group by id) as x
on x.id = t1.id

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 14:48:43

select t1.id, t1.customer, min(t2.salesdate) as MinSalesDate
from table1 as t1
inner join table2 as t2 on t2.id = t1.id
group by t1.id, t1.customer

Peter Larsson
Helsingborg, Sweden

Hippi
Yak Posting Veteran

63 Posts

Posted - 2007-04-04 : 19:24:58

select id,Customer, (select min(Saledate) from Sales where Sales.Id=Customer.Id )
from Customer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-04 : 21:32:11