Your co-worker Terri, who is just learning Microsoft SQL, is preparing a few different reports and sends you a query she has been working on. She needs assistance getting it to properly work. The issue is that she wants to generate a list of all customers who were entered into the Overdue_Accounts table but have presently paid their bill in full. Unfortunately, the query currently returns all records for any customer who has any account in the list of overdue accounts. Terri only wants it to return records when the customer has paid the bill in full. The second issue is that Terri wants to look up the customer's name from the Customer table instead of just displaying a orderID and customerID. She cannot figure out how to do it as of yet.
Terris Query currently returns all overdue account records for any customer who has even one line that has been paid off
SELECT [url removed, login to view], [url removed, login to view]
FROM Orders a
WHERE [url removed, login to view] in
( SELECT [url removed, login to view]
FROM [Overdue_Accounts] b
WHERE [url removed, login to view] = 1
You begin to think that a correlated sub-query is the answer to her problem. Revise Terris query to be a correlated sub-query that performs in the manner that she is wanting. (Here's a Hint: Using the test data you have been provided with, it should only return one record.)
The second part of Terris request is a little more difficult. You will need to join the Customers table to get the CompanyName field from it. Add a second query to your response that accesses the CompanyName using a join command but that still uses a correlated sub-query.
Finally, because you are not entirely sure if a correlated subquery is the best solution, rewrite the query a third and final time but instead of a correlated sub-query, use table joins.