Select the minimum value for each row join by another table
Use:
select t2.Name, t2.Dept, min(t1.Record)
from table1 t1
join table2 t2 on t2.ID = t1.ID
group by t2.ID, t2.Name, t2.Dept
or
select t2.Name, t2.Dept, a.record
from table2 t2
join
(
select t1.ID, min(t1.Record) [record]
from table1 t1
group by t1.ID
)a
on a.ID = t2.ID
For filtering add where
clause, e.g.:
select t2.Name, t2.Dept, min(t1.Record)
from table1 t1
join table2 t2 on t2.ID = t1.ID
where t1.Date = '11/7/2013'
group by t2.ID, t2.Name, t2.Dept
Please try:
Select
b.Name,
b.Dept,
MIN(Record) Record
from
Table1 a join Table2 b on a.ID=b.ID
GROUP BY b.Name,
b.Dept