Jump to content

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


sptrsn

Recommended Posts

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.  :P

 

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.