CODEWITHSUNDEEP
sqljoin
Kiril
Kiril

Reputation: 40345

Multiple join statements not returning the expected result

I have to write a query with the following requirements:

The query should return a list of all entry values for a customer named “Steve” and for each date shown (if available) the most recent status detail for that date.

Customer Table 

CustomerID | CustomerName
1          |    Steve
2          |    John

Entry 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

Status Table

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

The expected output is:

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 that the expected output may be wrong and it should actually 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 the requirement, I don't understand why the 3/4/2010 date would occur twice and the second time it would 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 this: 

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 on what I'm doing wrong here? I can't figure it out...

Update I've changed the order to an entry so it doesn't confuse us that much.

Upvotes: 4

Views: 194

Answers (4)

Galz
Galz

Reputation: 6832

You are getting more results than you expect because the second JOIN condition is satisfied by many rows in the statuses table (e.g. There are 3 statusDates earlier than 5/4 so this date appears 3 times in the result set).

You need to JOIN statuses table, but get only one match (the latest). This can be done in several ways, AFAIK usually with a sub query. I think your case is rather complicated - I used a temp table. Hope it helps... (I currently don't have a DB to test this on, hopefully there are no silly 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);

To refrain from creating a temp table I believe this will work (please try and do 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);

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

You can use a 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')

Upvotes: 1

Rob
Rob

Reputation: 27357

The expected output is wrong. The last row should be for the date 2/4/2010. Also, their order costs are not right either. 2/4/2010 should be returning null, because there is no matching status.

Upvotes: 0

Ewan Heming
Ewan Heming

Reputation: 4648

Shouldn't the status date come after the order date? Something like:

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

Also, the CustomerID in the Status table seems a bit strange as it's usually orders that have a status, not the customer. Shouldn't the status table have an OrderID field?

Upvotes: 0

Related Questions