[求助]请问在pb6.5中如何使用全连接？
我目前使用的是pb6.5,连接的是oracle8.0.5<br/>假设有两个表<br/> table A table B<br/> x x <br/> ------- -------- <br/> 1 2 <br/> 2 5 <br/> 5 7<br/> 6 9 <br/>我想得到的结果是：<br/> <br/> 表C<br/>==================================================<br/> A.x B.x<br/> ------- ---------<br/> 1 <br/> 2 2<br/> 5 5 <br/> 6 <br/> 7<br/> 9<br/>==================================================<br/>当使用<br/>select A.x , B.x <br/> from A,B<br/>where A.x(+) = B.x(+);<br/>时，提示：<br/>ORA-01468:a predicate may reference only one outer-joined table<br/>请问我应该怎么办啊？<br/>使用A.x=B.x(+)或A.x(+)=B.x时是正常的。<br/>[此贴子已经被作者于2007-9-19 15:56:20编辑过]
<p>使用join应该可以解决了,参看</p><p><a href="http://www.w3schools.com/sql/sql_join.asp">http://www.w3schools.com/sql/sql_join.asp</a></p>
SQL JOIN
<strong><h2>Joins and Keys </h2><p>Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join. </p><p>Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table. </p><p>In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that <b>no</b> two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name. </p><p>When you look at the example tables below, notice that: </p><ul><li>The "Employee_ID" column is the primary key of the "Employees" table </li><li>The "Prod_ID" column is the primary key of the "Orders" table </li><li>The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names </li></ul><hr/><p><b>Employees</b>:</p><p><table class="ex" cellspacing="0" width="60%" border="1"><tbody><tr><th align="left" width="25%">Employee_ID</th><th align="left" width="35%">Name</th></tr><tr><td>01</td><td>Hansen, Ola</td></tr><tr><td>02</td><td>Svendson, Tove</td></tr><tr><td>03</td><td>Svendson, Stephen</td></tr><tr><td>04</td><td>Pettersen, Kari</td></tr></tbody></table></p><p><b>Orders:</b></p><p><table class="ex" cellspacing="0" width="60%" border="1"><tbody><tr><th align="left" width="15%">Prod_ID</th><th align="left" width="20%">Product</th><th align="left" width="25%">Employee_ID</th></tr><tr><td>234</td><td>Printer</td><td>01</td></tr><tr><td>657</td><td>Table</td><td>03</td></tr><tr><td>865</td><td>Chair</td><td>03</td></tr></tbody></table><br/></p><hr/><h2>Referring to Two Tables</h2><p>We can select data from two tables by referring to two tables, like this:</p><h3>Example</h3><p>Who has ordered a product, and what did they order?</p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT Employees.Name, Orders.ProductFROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID</pre></td></tr></tbody></table></p><p><b>Result</b></p><p><table class="ex" cellspacing="0" width="60%" border="1"><tbody><tr><th align="left" width="35%"><b>Name</b></th><th align="left" width="25%">Product</th></tr><tr><td>Hansen, Ola</td><td>Printer</td></tr><tr><td>Svendson, Stephen</td><td>Table</td></tr><tr><td>Svendson, Stephen</td><td>Chair</td></tr></tbody></table></p><h3>Example</h3><p>Who ordered a printer?</p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND Orders.Product='Printer'</pre></td></tr></tbody></table></p><p><b>Result</b></p><p><table class="ex" cellspacing="0" width="60%" border="1"><tbody><tr><th align="left"><b>Name</b></th></tr><tr><td>Hansen, Ola</td></tr></tbody></table><br/></p><hr/><h2>Using Joins</h2><p>OR we can select data from two tables with the JOIN keyword, like this:</p><h3>Example INNER JOIN</h3><p><b>Syntax</b></p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield</pre></td></tr></tbody></table></p><p>Who has ordered a product, and what did they order?</p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID</pre></td></tr></tbody></table></p><p>The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will <b>not</b> be listed.</p><p><b>Result</b></p><p><table class="ex" cellspacing="0" width="60%" border="1"><tbody><tr><th align="left" width="35%"><b>Name</b></th><th align="left" width="25%">Product</th></tr><tr><td>Hansen, Ola</td><td>Printer</td></tr><tr><td>Svendson, Stephen</td><td>Table</td></tr><tr><td>Svendson, Stephen</td><td>Chair</td></tr></tbody></table></p><h3>Example LEFT JOIN</h3><p><b>Syntax</b></p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield</pre></td></tr></tbody></table></p><p>List all employees, and their orders - if any.</p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID</pre></td></tr></tbody></table></p><p>The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows <b>also</b> will be listed.</p><p><b>Result</b></p><p><table class="ex" cellspacing="0" width="60%" border="1"><tbody><tr><th align="left" width="35%"><b>Name</b></th><th align="left" width="25%">Product</th></tr><tr><td>Hansen, Ola</td><td>Printer</td></tr><tr><td>Svendson, Tove</td><td> </td></tr><tr><td>Svendson, Stephen</td><td>Table</td></tr><tr><td>Svendson, Stephen</td><td>Chair</td></tr><tr><td>Pettersen, Kari</td><td> </td></tr></tbody></table></p><h3>Example RIGHT JOIN</h3><p><b>Syntax</b></p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield</pre></td></tr></tbody></table></p><p>List all orders, and who has ordered - if any.</p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID</pre></td></tr></tbody></table></p><p>The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows <b>also</b> would have been listed.</p><p><b>Result</b></p><p><table class="ex" cellspacing="0" width="60%" border="1"><tbody><tr><th align="left" width="35%"><b>Name</b></th><th align="left" width="25%">Product</th></tr><tr><td>Hansen, Ola</td><td>Printer</td></tr><tr><td>Svendson, Stephen</td><td>Table</td></tr><tr><td>Svendson, Stephen</td><td>Chair</td></tr></tbody></table></p><h3>Example</h3><p>Who ordered a printer?</p><p><table class="ex" cellspacing="0" width="100%" border="1"><tbody><tr><td><pre>SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE Orders.Product = 'Printer'</pre></td></tr></tbody></table></p><p><b>Result</b></p><p><table class="ex" cellspacing="0" width="60%" border="1"><tbody><tr><th align="left"><b>Name</b></th></tr><tr><td>Hansen, Ola</td></tr></tbody></table></p><p></p><p>不错的样子，学习！</p></strong>
页:
[1]