jeudi 2 avril 2015

How to convert this subquery in a join?

Using this SO answer I've created the following query:



select
created, property_id, requesting_user_id, type, response
from
pdr t1
where
t1.created = (
select max(created)
from pdr t2
where t2.property_id = t1.property_id and t2.requesting_user_id = t1.requesting_user_id
)


This works like a charm, but now I want to transform this (also as suggested in the SO answer I linked above) to a query using a join. So I came up with this:



select
created, property_id, requesting_user_id, type, response
from
pdr t1
inner join (
select max(created) as created, property_id, requesting_user_id
from pdr
group by property_id, requesting_user_id
) as t2 on t2.property_id = t1.property_id and t2.requesting_user_id = t1.requesting_user_id and t2.created = t1.created


Unfortunately this returns an error saying ambiguous column name: created. So I messed around with putting t1. or t2. before some of the created things, but then I get all sorts of syntax errors, so I'm kinda lost here.


Could anybody help me out in what I'm doing wrong here? All tips are welcome!


ps: I'm currently testing this on SQLite, but in the end it should work on MySQL as well. If there is a difference that would of course be very interesting to know also.


Aucun commentaire:

Enregistrer un commentaire