Complex SQL Join?

I have a database similar to the below structure, although I’ve simplified it a little…

– Customers
CustomerID, CustomerType
1001, Retail

– CustomerNames
CustomerID, NameID, Name, StartDate, EndDate
1001, 2001, Test A1, 01/01/2015, 31/12/2015
1001, 2002, Test A2, 01/01/2016, 31/12/2016

– Transactions
TransactionID, CustomerNameID, TransactionValue
100000, 2001, £20.00
100001, 2002, £10.00

I need to join all the three tables together to get a list of transactions, but when the “customer name” changes a new record is created in CustomerNames and the transactions hang off the customer name table instead of the customers table (not my design!).

How do I join all the three tables to show me all transactions for all “Retail” customers? I would be expecting to see CustomerID, latest Customer Name, and the single transactions.

i’ll walk you through this one step at a time (assuming Molona doesn’t jump in and hand you the final solution before we’re done ;))

first, can you write a query that gets each retail customer’s latest name?

This is the only way I could do it, but it’s dirty…

SELECT	cu.CustomerID,
		Name
FROM	Customers cu
JOIN (	SELECT		CustomerID,
					MAX(CustomerNameID) 'CustomerNameID'
		FROM		CustomerNames
		GROUP BY	CustomerID
) cn ON cn.CustomerID = cu.CustomerID
JOIN (	SELECT		Name,
					CustomerNameID
		FROM		CustomerNames
) cnn ON cnn.CustomerNameID = cn. CustomerNameID

does it produce the right results? then that’s the main objective

but you have one join too many :wink:

also, relying on an auto_increment to determine sequence is an sql anti-pattern – use a date column instead

It does produce what you have asked for.

If I did a MAX(EndDate) instead, I would still have to include the CustomerNameID, which would list everything, in which case I might as well just have stuck with a simple join (which doesn’t work).

How have I got too many joins? Can you get the latest customer name from 1 derived query whilst have two columns in the select?

Finally, like I said before, I don’t have control over the design and I can’t go editing the tables.

okay, i think i misled myself slightly, and you were right

it takes only one table (referenced twice, once in a subquery) in order to get the latest name for each customer, but then we need the additional join to the customers table to filter out the retail customers

here’s the steps i had in mind –

first, get latest date for each customerID –

SELECT CustomerID , MAX(StartDate) AS latest FROM CustomerNames GROUP BY CustomerID

now join this back to get the name corresponding to the latest date –

SELECT n.CustomerID , n.Name , n.StartDate FROM ( SELECT CustomerID , MAX(StartDate) AS latest FROM CustomerNames GROUP BY CustomerID ) AS m INNER JOIN CustomerNames AS n ON n.CustomerID = m.CustomerID AND n.StartDate = m.latest

so far, one join and only one table

then join to the customers table –

SELECT n.CustomerID , n.Name , n.StartDate FROM ( SELECT CustomerID , MAX(StartDate) AS latest FROM CustomerNames GROUP BY CustomerID ) AS m INNER JOIN CustomerNames AS n ON n.CustomerID = m.CustomerID AND n.StartDate = m.latest INNER JOIN Customers AS c ON c.CustomerID = m.CustomerID AND c.CustomerType = 'Retail'

and finally join to the Transcations table –

SELECT n.CustomerID , n.Name , n.StartDate , t.TransactionID , t.TransactionValue FROM ( SELECT CustomerID , MAX(StartDate) AS latest FROM CustomerNames GROUP BY CustomerID ) AS m INNER JOIN CustomerNames AS n ON n.CustomerID = m.CustomerID AND n.StartDate = m.latest INNER JOIN Customers AS c ON c.CustomerID = m.CustomerID AND c.CustomerType = 'Retail' INNER JOIN Transactions AS t ON t.CustomerNameID = n.NameID

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.