Multiple join statements do not return the expected result

Question

I need to write a request with the following requirements:

The request should return a list of all input values ​​for the client named "Steve" and for each specified date (if available) the latest status information for that date.

Customer table

CustomerID | CustomerName 1 | Steve 2 | John 

Records table

 CustomerID | EntryDate | EntryValue 1 | 5/4/2010 | 200.0 1 | 4/4/2010 | 100.0 1 | 3/4/2010 | 150.0 1 | 2/4/2010 | 170.0 2 | 5/4/2010 | 220.0 

State table

 CustomerID | StatusDate | Detail 1 | 5/28/2010 | D 1 | 4/24/2010 | S 1 | 4/5/2010 | P 1 | 2/28/2010 | A 

Expected Result:

 CustomerName | Date | OrderCost | Detail Steve | 5/4/2010 | 200.0 | S Steve | 4/4/2010 | 100.0 | A Steve | 3/4/2010 | 75.0 | A Steve | 3/4/2010 | 75.0 | <null> 

I think the expected result may be wrong, and in fact it should be:

 CustomerName | Date | OrderCost | Detail Steve | 5/4/2010 | 200.0 | S Steve | 4/4/2010 | 100.0 | A Steve | 3/4/2010 | 150.0 | A Steve | 2/4/2010 | 170.0 | <null> 

Given this requirement, I do not understand why the date 3/4/2010 will occur twice, and the second time it will have a Detail. I wrote the following query:

I wrote the following query:

 SELECT Customers.CustomerName, Entries.EntryDate, Entries.EntryValue, Status.Detail FROM Customers INNER JOIN Entries ON Customers.CustomerID = Entries.CustomerID LEFT OUTER JOIN Status ON Status.CustomerID = Customers.CustomersID AND Status.StatusDate <= Entries.EntryDate WHERE (Customers.CustomerName = 'Steve') 

The result of my query is as follows:

 CustomerName| EntryDate | EntryValue | Detail Steve | 5/4/2010 | 200.00 | S Steve | 5/4/2010 | 200.00 | P Steve | 5/4/2010 | 200.00 | A Steve | 4/4/2010 | 100.00 | A Steve | 3/4/2010 | 150.00 | A Steve | 2/4/2010 | 170.00 | NULL 

Any hints of what I'm doing wrong here? I can't figure it out ...

Update I changed the order on the record, so it does not confuse us too much.

Answer 1

You get more results than you expect, because the second JOIN condition is satisfied by many rows in the status table (for example, there are 3 statusDates before 5/4, so this date appears 3 times in the result set).

You need to connect to the status table, but get only one match (the last). There are several ways to do this: AFAIK, usually with a sub-query. I think your case is rather complicated - I used a temporary table. Hope this helps ... (I currently do not have a DB to check this, I hope there are no stupid syntax errors).

 DROP TABLE IF EXISTS temp; CREATE TABLE temp AS -- This temp table is basically the result set you got (SELECT c.CustomerName, e.EntryDate, e.EntryValue, s.Detail, s.StatusDate FROM Customers c INNER JOIN Entires e ON c.CustomerID = e.CustomerID LEFT OUTER JOIN Status s ON s.CustomerID = c.CustomersID AND s.StatusDate <= e.EntryDate WHERE (c.CustomerName = 'Steve') ); SELECT t.CustomerName, t.EntryDate, t.EntryValue, t.Detail FROM temp t WHERE t.StatusDate = (SELECT MAX(t2.StatusDate) FROM temp t2 WHERE t2.EntryDate = t.EntryDate); 

In order not to create a temporary table, I believe that this will work (please try and let me know!)

 SELECT t.CustomerName, t.EntryDate, t.EntryValue, t.Detail FROM (SELECT c.CustomerName, e.EntryDate, e.EntryValue, s.Detail, s.StatusDate FROM Customers c INNER JOIN Entries e ON c.CustomerID = e.CustomerID LEFT OUTER JOIN Status s ON s.CustomerID = c.CustomersID AND s.StatusDate <= e.EntryDate WHERE c.CustomerName = 'Steve') AS t WHERE t.StatusDate = (SELECT MAX(t2.StatusDate) FROM temp t2 WHERE t2.EntryDate = t.EntryDate); 

Answer 2

You can use the subquery to get the status.
Use TOP 1 for SQL Server or LIMIT 1 for SQLite / MySQL

SQL Server / SyBase

 SELECT Customers.CustomerName, Entries.EntryDate, Entries.EntryValue, (SELECT top 1 Status.Detail From Status where Status.CustomerID = Customers.CustomersID AND Status.StatusDate <= Entries.EntryDate order by Status.StatusDate desc) FROM Customers INNER JOIN Entries ON Customers.CustomerID = Entries.CustomerID WHERE (Customers.CustomerName = 'Steve') 

MySQL / SQLite

 SELECT Customers.CustomerName, Entries.EntryDate, Entries.EntryValue, (SELECT Status.Detail From Status where Status.CustomerID = Customers.CustomersID AND Status.StatusDate <= Entries.EntryDate order by Status.StatusDate desc limit 1) FROM Customers INNER JOIN Entries ON Customers.CustomerID = Entries.CustomerID WHERE (Customers.CustomerName = 'Steve') 

Answer 3

Does the status date have a date after the order date? Sort of:

 SELECT Customers.CustomerName, Orders.OrderDate, Orders.OrderCost, Status.Detail FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID LEFT OUTER JOIN Status ON Status.CustomerID = Customers.CustomersID WHERE Customers.CustomerName = 'Steve' AND Status.StatusDate >= Orders.OrderDate 

In addition, the CustomerID in the Status table seems a little strange, since it usually orders a status, not a customer. Should the status table have an OrderID field?

Answer 4

The expected result is incorrect. The last line should be on the date 2/4/2010. In addition, their order value is not correct. 2/4/2010 should return null because there is no corresponding state.