多表关联的sql语句
表table1
newcode iname
20071105 t2
20076789 t3
20071882 t4
表table2
newcode iiname
20071105 t5
20071105 t6

表table3
newcode iiiname
20071105 t7
20071105 t8
20071105 t9

表tabe1的newcode是唯一的，没有重复，其他两个表的newcode与table1对应，可以重复（例如表table2有两个newcode为20071105的数据），现在我要得到如下结果
newcode iname iiname iiiname
20071105 t2 t5 t7
当newcode=20071105,从表table2中找newcode为20071105的所有值，但只取第一条
table3同上。

[解决办法]

SQL code

select newcode,iname,max(iiname),max(iiiname)from (select t1.newcode,iname,iiname,iiiname      from table1 t1,table2 t2,table3 t3      where t1.newcode=t2.newcode and t1.newcode=t3.newcode) tgroup by newcode,iname;select newcode,iname,min(iiname),min(iiiname)from (select t1.newcode,iname,iiname,iiiname      from table1 t1,table2 t2,table3 t3      where t1.newcode=t2.newcode and t1.newcode=t3.newcode) tgroup by newcode,iname;
[解决办法]
SQL code
select t1.newcode, t1.iname, t2.iiname, t3.iiinamefrom table1 t1 join (select newcode, min(iiname) from table2 group by newcode) t2 on t1.newcode=t2.newcode    join (select newcode, min(iiiname) from table3 group by newcode) t3 on t1.newcode=t3.newcode