Josh Berkus wrote:
> Joseph,
>
> Please take a look at my example again:
>
>
>>>SELECT tbla.a, tbla.b, total_b
>>>FROM tbla,
>>> (SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot
>>>WHERE tbla.b = b_tot.b
>>>
>>>
>
>>OK when I tried to convert the query:
>>select u.field1, ... , d.field1, ..., mll.lastml FROM utable u,
>>dtable d,(select ml.field
>>as lastml from ml WHERE ml.key = u.key order by ml.keyfield desc
>>limit 1) mll WHERE u.key
>>= d.key and ... and lastml = 2;
>>
>
> You need to put the JOIN condition from the subselect ("ml.key = u.key")
> OUTSIDE the subselect ("ml.key = mll.key"). You can't reference columns
> from the main select inside a subselect except in an EXISTS clause.
Actually I do it all the time, in the select part.
>
> This does mean that you're going to need some approach other than the
> "limit 1" to limit the rows in your result set. Probably at GROUP BY.
Well that is the problem. In my subselect I only want the latest value of ml.field,
which
I get my ordering my ml.keyfield. I don't see how group by could help. I would need
to
do something like: ... WHERE mll.keyfield = (select ml.keyfield from mltable ml where
...
order by ml.keyfield desc limit 1) which would be a subselect again which is what I'm
trying to avoid.
SQL is really annoying.
>
> -Josh
>
> P.S. I'm sure you don't mean for your e-mails to come across as
> antagonistic when you are asking for help. Can you please be careful of
> your phrasing?
I certainly didn't mean to be antagonistic, and looking at the email I sent I don't
see
why you thought it was.
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
