tailieunhanh - SQL VISUAL QUICKSTART GUIDE- P49

SQL VISUAL QUICKSTART GUIDE- P49:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 15 Listing traverses the hierarchy by using multiple self-joins to trace the chain of command from employee WS3 to the top of the tree. See Figure for the result. Unfortunately you must know the depth of the hierarchy before you write this query use one of the alternatives given in the tip if possible. Listing Show the full hierarchical relationship of employee WS3. See Figure for the result. Listing SELECT II II II II II II AS chain_of_command FROM hier hl hier h2 hier h3 hier h4 WHERE WS3 AND AND AND chain_of_command WS3 DIR1 VP1 CEO Figure Result of Listing . Working with Hierarchies 460 SQL Tricks Tip DBMS To run Listing in Microsoft Access and Microsoft SQL Server change each II to . In MySQL use CONCATO to concatenate strings. See Concatenating Strings with II in Chapter 5. In Microsoft SQL Server and DB2 use the standard recursive WITH clause to traverse a hierarchy. The following query is equivalent to Listing in SQL Server change each II to . WITH recurse chain emp_level boss_id AS SELECT CAST emp_title AS VARCHAR 50 0 boss_id FROM hier WHERE emp_title WS3 UNION ALL SELECT CAST II II AS VARCHAR 50 1 FROM hier recurse WHERE SELECT chain AS chain_of_command FROM recurse WHERE emp_level 3 In Microsoft SQL Server and DB2 to list everyone who reports to a particular employee VP1 in this example either directly or indirectly through a boss s boss use this query WITH recurse emp_title emp_id AS SELECT emp_title emp_id FROM hier WHERE emp_title VP1 UNION ALL SELECT FROM hier recurse WHERE SELECT emp_title AS Works for VP1 FROM recurse WHERE emp_title VP1 In Oracle 10g or later use the nonstandard CONNECT BY syntax to traverse a hierarchy. The .