select t1.* ,t2.personName as PersonName1 ,t3.PersonName as PersonName2 from table1 t1 ,table2 t2,table2 t3 where t1.OperatorID1 = t2.PersonID and t1.OperatorID1 = t2.PersonID(+)这下应该可以了吧。
不好意思,刚才写的有笔误。 select t1.* ,t2.personName as PersonName1 ,t3.PersonName as PersonName2 from table1 t1 ,table2 t2,table2 t3 where t1.OperatorID1 = t2.PersonID and t1.OperatorID1 = t3.PersonID(+)
如果table2中的PersonID已经使用, 就不应该能被删除。 可以通过外键约束来限制。 你的要求可以用: select t1.* ,t2.personName as PersonName1 ,t3.PersonName as PersonName2 from table1 t1 ,table2 t2,table2 t3 where t1.OperatorID1 = t2.PersonID(+) and t1.OperatorID2= t3.PersonID(+); 来实现
FROM TABLE1, TABLE2
WHERE TABLE1.PersonID=TABLE2.PersonID(+)
正面的是ORACLE独家SQL语法
ORACLE:
SELECT TABLE1.*,TABLE2.PersonName
FROM TABLE1, TABLE2
WHERE TABLE1.OperatorID=TABLE2.PersonID(+)MS SQL SERVER ,ACCESS
SELECT TABLE1.*,TABLE2.PersonName
FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.OperatorID=TABLE2.PersonID(+)
(注,ACCESS要使用关健字OUTER,而在MS SQL SERVER中则可以省略)别,MS SQL SERVER也有自己的外联接语法
*=左联接,相当于LEFT OUTER JOIN
=*右联接,相当于RIGHT OUTER JOIN
SELECT TABLE1.*,TABLE2.PersonName
FROM TABLE1, TABLE2
WHERE TABLE1.OperatorID*=TABLE2.PersonID你自己可以分别试一试
写出来我将帖子加到100分。
from table1 t1 ,table2 t2,table2 t3
where t1.OperatorID1 = t2.PersonID and t1.OperatorID1 = t2.PersonID(+)这下应该可以了吧。
select t1.* ,t2.personName as PersonName1 ,t3.PersonName as PersonName2
from table1 t1 ,table2 t2,table2 t3
where t1.OperatorID1 = t2.PersonID and t1.OperatorID1 = t3.PersonID(+)
可以通过外键约束来限制。
你的要求可以用:
select t1.* ,t2.personName as PersonName1 ,t3.PersonName as PersonName2
from table1 t1 ,table2 t2,table2 t3
where t1.OperatorID1 = t2.PersonID(+) and t1.OperatorID2= t3.PersonID(+);
来实现