Does not Recognize Column in Where Clause when Joining Tables

Question

SELECT * FROM a 
JOIN (SELECT * FROM b WHERE b.aId = a.Id) AS c ON c.aId = a.Id

It says does not recognize: a.Id in the Where Clause.

I know its probably cause im using a temp table and a.Id cannot be passed through but is there any way we can do that?

Because here is what actually happens

SELECT * 
  FROM a 
  JOIN (SELECT * FROM b 
         WHERE b.aId = a.Id 
      ORDER BY b.dateReg DESC
         LIMIT 1) AS c ON c.aId = a.Id

I need the ORDER BY b.dateReg DESC LIMIT 1 as it returns me the last row that assosiates with the a Table.. If you require i can post the Create Query

Answer 1

-- find last rows on b

select * from b x 
where exists(
     select id 
     from b y 
     where y.id = b.id 
     having max(y.dateReg) = x.dateReg
     group by id
     )

-- then join that b to a, this is the final query:

select * from a
join 
(
     select * from b x 
     where exists(
         select id 
         from b y 
         where y.id = b.id 
         having max(y.dateReg) = x.dateReg
         group by id
         )

) as last_rows on last_rows.id = a.id

-- simpler:

select * 
from a join b x on a.id = x.id
where exists(
    select id
    from b y
    where y.id = b.id
    having max(y.dateReg) = x.dateReg
    group by id)

-- or if you will use postgres:

select DISTINCT ON (a.id) * 
from a join b x on a.id = x.id
order by a.id, b.dateReg DESC
-- look ma! no group by!    

-- nothing beats postgresql's simplicity :-)

Answer 2

Try:

SELECT DISTINCT *
  FROM A
  JOIN B b ON b.aid = a.id
  JOIN (SELECT b.aid,
               MAX(b.datereg) 'max_datereg'
          FROM B b
      GROUP BY b.aid) md ON md.aid = b.aid
                        AND md.max_datereg = b.datereg

If you do want the first record associated with the associate, use:

SELECT DISTINCT *
  FROM A
  JOIN B b ON b.aid = a.id
  JOIN (SELECT b.aid,
               MIN(b.datereg) 'min_datereg'
          FROM B b
      GROUP BY b.aid) md ON md.aid = b.aid
                        AND md.min_datereg = b.datereg