Re: What is *wrong* with this query???

On Friday, November 04, 2011 9:51:14 pm Steve Murphy wrote:
> I give! I'm flummoxed!
>
> Here is what I have, 3 tables:
>
> schedule
> company building status0
> 3 x active
> 4 x active
> 5 x active
> 3 x active
> 3 x active
> 3 x active
>
> In the end, I want to replace the building id's above. They start out with
> the non-informative value of '1';
>
> company
> id name status
> 3 x active
> 4 y active
> 5 z active
>
>
> building
> id company name
> 1 3 A active
> 2 3 B active
> 3 3 C active
> 4 4 D active
> 5 4 E active
> 6 4 F active
> 7 5 G active
> 8 5 H active
> 9 5 I active
>
> So, every company has 3 schedules. Of the 3, I'd like to select the one
> with the lowest id.
>
> I'm using postgresql 8.1. Yes, ancient, but I have no choice in this
> affair.
>
> select schedule.id as sched_id, bld.id as bid
> from
> schedule
> left join company on schedule.company = company.id
> left join (select * from building where building.company =
> company.id order by id limit 1) as bld where
> schedule.status = 'active' and company.status = 'active' and
> bld.status = 'active';
>
> I get a syntax error on the the "where".
>
> ERROR: syntax error at or near "where" at character ….
> LINE 6: where
> ^
>
> If I leave out the where clause entirely, that's an error also,
> ERROR: syntax error at or near ";" at character ….
> LINE 5: … as bld ;
> ^ <it's right under the
> semicolon>
>
> So, it's expecting ***SOMETHING*** after the "as bld", but it sure isn't
> going to tell me what. What am I missing?

on something=something_else
--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com