So close. Just a little help with joining a table with a subquery please.

[sptrsn]

Ok, I finally figured out a way to use the max() function and get the right result and this query works great. Now I need to join it with another table, but I keep getting a duplicate column name error. specifically 'apn'

here's my query that works...

SELECT * from prop d inner join (SELECT apn, max(bid) b FROM `prop` GROUP BY apn)p on d.bid=p.b where bidstatus='active'

 

I want to join another table on the apn number. Here's what I have that gives me a duplicate column name 'apn'

 

Line 2 is the subquery that works, alias 'z'

 

select * from nvcdata n join 
(SELECT* from prop d inner join (SELECT apn, max(bid) b FROM `prop` GROUP BY apn)p on d.bid=p.b where bidstatus='active')z 
on n.apn=z.apn

 

I would sure be grateful for any insight you might be able to offer. Up to and including, "What? Are you nuts? You can't do that." It's only ok to say that if you'll rewrite it for me though. 

 

[sptrsn]

I got it. Thanks.

select * from nvcdata n join 
(SELECT* from prop d inner join (SELECT max(bid) b FROM `prop` GROUP BY apn)p on d.bid=p.b where bidstatus='active')z 
on n.apn=z.apn

 

had to get rid of the that extra apn field select inside the subquery.

[kickstart]

Hi

 

Keep the apn inside the sub query but specify the columns you require in the SELECTs rather than using SELECT *.

 

All the best

 

Keith