Given are two tables,
Table A containing customerid, lastchange, internallink
Table B containing internallink, turnover
(I'm simplyfying here and changing it to a generic example, the actual structure is more complex. SQL dialect for now is mySQL.)
The only unique thing (per table) is the internallink. There are several records in A with the same customerID, different dates in lastchange, and different internallink values. There are other items linked with this; I cannot change the tables.
I need the recordIDs from A which are the most recent for a customer (highest lastchange value of all with the same customerID) and to which the entries in B which match a certain value condition are linked.
I think the
SELECT `internallink` FROM `B` WHERE (`turnover` > 10000)
part is not the issue.
I got this far:
SELECT `customerID`, MAX(`lastchange`)
FROM `A`
WHERE `lastchange` IN (SELECT `internallink` FROM `B`
WHERE `turnover` > 10000)
GROUP BY `customerID`;
Alas, that statement gives wrong results, because above will return me customerIDs for which the most recent value does not fulfill the criteria, but some older did - it selects the oldest that did, and returns this. But in case the most recent entry is below threshold, the customerID should not turn up at all.
Where did I go wrong, and what is the correct approach to this?
Sample Data Table A
customerid lastchange internallink
3 2010-02-11 11
3 2010-09-04 12
3 2010-10-22 13
3 2010-11-23 14
4 2010-05-05 15
4 2010-12-01 16
5 2010-11-28 17
5 2010-11-29 18
Table B
internallink turnover
11 47000
12 11000
13 8000
14 15000
15 17000
16 23000
17 50000
18 10000
The actual threshold in my tests is 12000. You can see customerID should not be in the result set, since the most recent entry is below the threshold.
The result set should be (3,2010-11-23)(4,2010-12-01) - but currently it also contains (5,2010-11-28), which is wrong.
Getting a bit closer (with your help, thanks!), these two statements both work:
SELECT customerID, MAX(lastchange), internallink FROM A GROUP BY customerID; SELECT internallink FROM B WHERE (turnover > 12000);
Now all I need is the intersection of both... with the correct logic!
After lots of testing and some research, I found this solution, and I post this in case anybody else should face a similar problem.
An additional table "cache" keeps copies of the most recent entries in table A, reducing the complexity a lot. It is kept current by using triggers like this:
CREATE TRIGGER sync_a_insert AFTER INSERT ON a FOR EACH ROW
INSERT INTO cache (`customerID`, `internallink`) VALUES (NEW.`customerID`,NEW.`internallink`);
CREATE TRIGGER sync_a_update AFTER UPDATE ON a FOR EACH ROW
UPDATE cache SET `internallink` = NEW.`internallink` WHERE (`customerID` = NEW.`customerID`);
CREATE TRIGGER sync_a_delete BEFORE DELETE ON a FOR EACH ROW
DELETE FROM cache WHERE `customerID` = OLD.`customerID`;
For INSERT and UPDATE, those triggers go off after the fact, so the entries in Table a are complete before the cache updates. For DELETE, the cache needs to be updated before the original entry vanishes.
Once this is in place, everything else becomes simple:
SELECT `customerID` FROM cache WHERE `internallink` IN
(SELECT `internallink` FROM b WHERE (`turnover` > 10000));
For me, this is a viable solution, and it even speeds up the lookups. Of course there is a cost in DB size, but I think the performance is much better overall - as long as there is at least one more read access than write access, there is an improvement.
The answers you gave were, however, very helpful to me. I learned a bunch of things from them and from trying to follow your advice (even putting some of it to use in other places already). Thanks to all who replied to my question!