Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 263
Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 263 Here s an example of using an ON clause to generate data for a custom order form: SELECT FirstName, LastName, ProductDescription FROM Person CROSS JOIN Product ORDER BY FirstName, LastName, ProductDescription The FROM clause of a SELECT statement is an incredibly complex piece of work. You have the different join types and their correlations all going on in there. So, how can you make it even more complex and harder to read? Derived Tables You can use derived tables to make your queries simpler to read, or at least simpler to write. To use a derived table, put a SELECT statement in parentheses in the FROM clause where you d normally put a table name. An alias is required for a derived table. Let s say that you have a query that looks something like this: SELECT Person.PersonID, FirstName, LastName, ProductID, .QtyPurchased FROM Sales RIGHT JOIN Person ON Person.PersonID = Sales.PersonID And you d like to get the address for the people returned as well. You ve already done this once in the earlier examples, but here s another way to do it: Select P.*, Address.StreetAddress, Address.City, .Address.ZipCode from (SELECT Person.PersonID, FirstName, LastName, .ProductID, QtyPurchased FROM Sales RIGHT JOIN Person ON Person.PersonID = Sales.PersonID) P INNER JOIN PersonAddress ON PersonAddress.PersonID = P.PersonID INNER JOIN Address ON Address.AddressID = PersonAddress. .AddressID
For high quality java hosting services please check tomcat web hosting website.