Re: What is *wrong* with this query??? - Mailing list pgsql-general

On Sat, Nov 5, 2011 at 10:51 AM, Steve Murphy <smurphy@intorrent.com> 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?

left join (select * from building where building.company = company.id
order by id limit 1)  as bld on (company.id = bld.company)

>
>
>
>
>
>



--
с уважением,
Таир Сабыргалиев
ТОО "BEE Software"
Республика Казахстан, 010000
г.Астана, ул.Сарайшык 34, ВП-27
Тел.: +7 (7172) 56-89-31
Сот.: +7 (702) 2173359
e-mail: tair.sabirgaliev@bee.kz
Tair Sabirgaliev
"BEE Software" Ltd.
Republic of Kazakhstan, 010000
Astana, Sarayshyk str. 34, sect. 27
Tel.: +7 (7172) 56-89-31
Mob.: +7 (702) 2173359
e-mail: tair.sabirgaliev@bee.kz