Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 261
Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 261 join type. Now here s a list of all the addresses in the database and what they bought, if they bought anything: SELECT A.*, S.ProductID, S.QtyPurchased FROM Person P LEFT JOIN Sales S ON P.PersonID = S.PersonID INNER JOIN PersonAddress PA ON PA.PersonID = P.PersonID INNER JOIN Address A on PA.AddressID = A.AddressID Now, the FULL OUTER JOIN. Imagine that you want to query all the records from Person and Sales, and have the tables put together where possible, but you want all records shown from both tables, regardless of whether there was a correlation or not. Here s how you can do it: SELECT FirstName, LastName, ProductID, QtyPurchased FROM Person FULL JOIN Sales ON Person.PersonID = Sales.PersonID That returns the following recordset: FirstName LastName ProductID QtyPurchased Danny Jones 37 4 Danny Jones 38 3 Melissa Jones NULL NULL Scott Smith 39 1 Alex Riley 51 1 Chris Avery NULL NULL Jennifer Avery NULL NULL Bryan Decker NULL NULL Robin Decker NULL NULL Shelly Alexander 37 10 Shelly Alexander 38 5 NULL NULL 41 6 Notice that you have examples where the data that comes from the Sales table (ProductID and QtyPurchased) is NULL, for Chris Avery and others, along with an example of ProductID of 41 and QtyPurchased of 6 that was purchased by someone not in the Person table. Filtering with Outer Joins One common use of an outer join is to find places where there aren t correlations in data. For example, if you wanted to find all the rows in the Person table that didn t have a corresponding row
If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.