1 / 33

Computer Science & Engineering 2111

Computer Science & Engineering 2111. Lecture 13 Outer Joins. Research Papers Database. Primary Keys. Foreign Keys. Inner Join between Client and Payments. Notice that only records with matching values in the foreign key fields of the related tables are included in the resulting dynaset.

belle
Download Presentation

Computer Science & Engineering 2111

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Computer Science & Engineering 2111 Lecture 13 Outer Joins CSE 2111 Outer Joins

  2. Research Papers Database Primary Keys Foreign Keys CSE 2111 Outer Joins

  3. Inner Join between Client and Payments Notice that only records with matching values in the foreign key fields of the related tables are included in the resulting dynaset Resulting Dynaset FK PK CSE 2111 Outer Joins

  4. Outer Join between Client and Payments Outer join relative to the Client table (the primary key side of the relationship) Resulting Dynaset CSE 2111 Outer Joins

  5. Outer Join between Client and Payments Outer join relative to the Payments table (the many side of the relationship) Notice the results look very similar to an inner join between the two tables with one major exception. Can you identify the exception? Resulting Dynaset CSE 2111 Outer Joins

  6. Using 3 or more tables in a query Let’s look at a Many-One-Many Relationship and see what happens If I needed to obtain records from my database that included the customer information (name and ID), total charges, total payments and balance due, I would need to run a query using these tables. What would happen? CSE 2111 Outer Joins

  7. Running a Query with Client, Charges and Payments: The Design View CSE 2111 Outer Joins

  8. First-what should the results look like? Client Resulting Dynaset Charges Payments CSE 2111 Outer Joins

  9. Now let’s see what really happens….. • Should Nancy have total charges of $750 and total payments of $700? • Where are the other clients? Karen Day has charges but no payments. Shouldn’t you want to see her in your results? Are there more people like this missing? What we WANT What we actually get CSE 2111 Outer Joins

  10. So what happened? Clients Intermediate Dynaset 1 Intermediate Dynaset 2 Charges Payments Aggregate functions applied/Expressions calculated Final Dynaset CSE 2111 Outer Joins

  11. Client Resulting Intermediate Dynaset 1 (Partial View) Charges CSE 2111 Outer Joins

  12. Intermediate Dynaset 1 (Partial View) Payments ? Resulting Intermediate Dynaset 2 CSE 2111 Outer Joins

  13. Aggregate functions & expressions are applied last: Resulting Intermediate Dynaset 2 Final Dynaset CSE 2111 Outer Joins

  14. 2 Major Problems exist with our results • Not all of the Clients show up in the final dynaset. • We used an Inner Join and got only those clients who are in both the Charges table and the Payments table. • Any ClientID that is not in all 3 tables will be left out of our final results. • For some of our clients, their charges and payments are wrong, resulting in an incorrect balance! CSE 2111 Outer Joins

  15. Would it help to use an Outer Join? Actually it did- but not enough. It solved the problem of not including everyone unless they were in all 3 tables. So now we see all clients, but some of them still have wrong values for charges and payments. Notice some of the values are correct making this a very dangerous problem. If you only spot check a few values, you might not see the problem. CSE 2111 Outer Joins

  16. Good news and Bad News • Good News: Access doesn’t always mess up queries with 3 or more tables in it. • We can predict and avoid this problem! • Bad News: You have to know what to look for to prevent this kind of problem from happening. CSE 2111 Outer Joins

  17. Using 3 or more tables in a query Let’s look at a One-Many-One Relationship and see what happens If I needed to obtain records from my database that included the customer information (name and ID), and the Method Type, I would need to run a query using these tables. What would happen? CSE 2111 Outer Joins

  18. Now let’s see what really happens….. What we WANT What we get CSE 2111 Outer Joins

  19. Client Payments Intermediate Dynaset 1 CSE 2111 Outer Joins

  20. Intermediate Dynaset 1 PaymentMethod Final Dynaset CSE 2111 Outer Joins

  21. So what’s the difference? Many-One-Many  NOT VALID!! One-Many-One OK! CSE 2111 Outer Joins

  22. So what do we do? Split up the relationship! Client 1 Client 1 ∞ ∞ Charges Payments SUMMARIZE CHARGES BY CLIENT SUMMARIZE PAYMENTS BY CLIENT CSE 2111 Outer Joins

  23. PaymentsByClient Tables: Client, Payments Join Type: Outer Join On: ClientID CSE 2111 Outer Joins

  24. ChargesByClient Tables: Client, Charges Join On: ClientID Join Type: Outer CSE 2111 Outer Joins

  25. ChargesByClient PaymentsByClient Notice that each client is listed exactly once in both queries. 1 1 ChargesByClient PaymentsByClient Join on ClientID Now we can put the relationship back together! CSE 2111 Outer Joins

  26. Now put the two summaries together & calculate the balance due… BalanceDue Tables: PaymentsByClient,ChargesByClient Join Type: Inner Join On: ClientID Balance: [Charges]![SumOfAmount] – [Payments]![SumOfAmount] CSE 2111 Outer Joins

  27. Are we there yet? Not quite….notice that Karen Day was charged $100 but hasn’t made a payment. Her balance should be $100 - $0 = $100, but it’s blank. Why? CSE 2111 Outer Joins

  28. Client Payments ? Let’s take a closer look at the PaymentsByClient Query….in an outer join with respect to Clients, when a record from Clients doesn’t have any matching records in Payments, it’s included in the results, but the fields that would have come from Payments are NULL. CSE 2111 Outer Joins

  29. Access doesn’t know what $100 – NULL is, so it punts and returns NULL as the result. But we know that in this case, NULL should be treated like zero – can we help Access out? CSE 2111 Outer Joins

  30. NZ Function Syntax: Nz(variant, value_if_null) If this argument evaluates to NULL…. Return this value If the variant argument does NOT evaluate to NULL, Nz will return whatever the variant argument does evaluate to. CSE 2111 Outer Joins

  31. Balance Due with the Nz function….. BalanceDue Tables: PaymentsByClient,ChargesByClient Join Type: Inner Join On: ClientID Balance: Nz([Charges]![SumOfAmount],0) – Nz([Payments]![SumOfAmount],0) CSE 2111 Outer Joins

  32. Finally! CSE 2111 Outer Joins

  33. Summary • Inner joins include only those combined records where the primary & foreign keys match. • Outer joins include all records from one of the tables, even if there isn’t a matching record in the other table. • Many-one-many relationships are not valid and must be broken down into multiple valid (one-many) relationships. • Use Nz to replace NULL values with zeroes. CSE 2111 Outer Joins

More Related