一个有关外连接的问题

Question

tableC有id、tableA_id、tableB_id？
LEFT JOIN是sql92标准写法FROM tableA,tableB是非sql92写法，两种写法混合使用容易出错的。
Oracle的写法是
SELECT tableA.id,tableB.id,count(tableC.id)
FROM tableA,tableB,tableC
WHERE tableC.tableA_id=talbeA.id(+) 
AND tableC.tableB_id=tableB.id
GROUP BY tableA.id,tableB.id

Answer 1

表的结构是这样的tableA
+---------+--------+
|   id    | name   |
+---------+--------+
|  1001   | eric   |
|  1002   | daniel |
|  1003   | paul   |
+---------+--------+tableB
+---------+--------+
|   id    | role   |
+---------+--------+
|  0001   | doctor |
|  0002   | teacher|
|  0003   | student|
+---------+--------+tableC
+---------+---------+---------+
| id      |tableAid |tableBid |
+---------+---------+---------+
|    1    |  1001   |  0001   |
|    2    |  1001   |  0002   |
|    3    |  1002   |  0003   |
|    4    |  1002   |  0002   |
|    5    |  1003   |  0001   |
|    6    |  1003   |  0001   |
|    7    |  1003   |  0003   |
+---------+---------+---------+我想要的结果是
+---------+---------+----------------+
|tableAid |tableBid |count(tableC.id)|
+---------+---------+----------------+
|  1001   |  0001   |  1             |
|  1001   |  0002   |  1             |
|  1001   |  0003   |  0             |
|  1002   |  0001   |  0             |
|  1002   |  0002   |  1             |
|  1002   |  0003   |  1             |
|  1003   |  0001   |  2             |
|  1003   |  0002   |  0             |
|  1003   |  0003   |  1             |
+---------+---------+----------------+

Answer 2

在MySql4.1.11下这样写就ok了
SELECT talbeA.id,tableB.id,count(tableC.id)
FROM talbeA,talbeB
LEFT JOIN tableC ON tableC.tableAid=talbeA.id AND tableC.tableBid=tableB.id
GROUP BY talbeA.id,tableB.id
在oracle下就不可以