Need to join three tables. Two from sub-queries.

[sptrsn]

I need to join three tables where t1.apn=t2.apn=t3.apn.

apn is already unique in table 1.

Tables 2 and 3 are sub-queries to get to a unique apn.

 

My sub-queries seem to work fine.

 

T2... 

select *, max(id) from nvcdata_history as T2 group by apn

and

T3... 

select *, max(bid) from prop as T3 group by apn

So, at the end of these two sub-queries, apn is unique in all three tables.

 

T1 is really the master list. (10k rows)

T2 is a history of changes. So there is a row inserted each time there is an update. (30k rows)

T3 is the small list of records I'm interested in fetching in this query. (100 rows)

 

Here is what I think should work. (obviously it doesn't or else I wouldn't be here)

select * from nvcdata as T1 join (select *, max(id) from nvcdata_history as T2 group by apn)on T1.apn=T2.apn join (select *, max(bid) from prop as T3 group by apn) on T2.apn=T3.apn

 

Most common error is #1248. Every derived tale must have it's own alias. Although I have had a plethora of others.

I don't recall which version. It's as recent as godaddy gets. (It sucks, but it's temp)

 

At the end of this, I should have the 100 records from T3. The 100 matching records from T1. And the 100 matching records from T2, where the record that is returned has the highest id (hence the most recently inserted/updated record)

 

Any bright ideas out there?

Speed will count here. I have another query that is not dissimilar and it takes 3-5 seconds. That's an eternity when your staring at your computer.

 

Thanks for any help.

 

 

 

 

[kickstart]

Hi

 

This might help

 

SELECT * 
FROM nvcdata as T1 
INNER JOIN (select apn, MAX(id) AS MaxId from nvcdata_history GROUP BY apn) T2 ON T1.apn=T2.apn 
INNER JOIN (select apn, MAX(bid) AS MaxBid from prop GROUP BY apn) T3 ON T2.apn=T3.apn
INNER JOIN nvcdata_history T4 ON T2.apn = T4.apn AND T2.MaxId = T4.id
INNER JOIN prop T5 ON T2.apn = T5.apn AND T2.MaxBid = T5.bid

 

Note that your original code main problem was that you hadn't given the subselects alias', you had just given the tables within the subselect an alias.

 

However the other issue was that you were selecting various columns in the subselect and using MAX aggregate function, but only having a single column in the GROUP BY clause. Most flavours of SQL will error on this but MySQL will cope, but the values of the other columns will be indeterminate. They will likely not relate to the row that the max id comes from.

 

All the best

 

Keith

[sptrsn]

Wow Keith! thank you so much for that. That helped in several ways.

to your point... it seems like anytime I have another field in the subquery, it is completely unrelated. That doesn't make a lot of sense.

 

Thanks for taking the time to understand and reply!

 

pm if you do any freelance.