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

What is *wrong* with this query???

From
Steve Murphy
Date:

 

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?

 

 

            

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

From
Scott Marlowe
Date:
On Fri, Nov 4, 2011 at 10:51 PM, Steve Murphy <smurphy@intorrent.com> wrote:
> 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';

Looks like you need an on clause after the second left join.

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

From
"David Johnston"
Date:

See embedded note after “as bld”

 

Dave

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Murphy
Sent: Saturday, November 05, 2011 12:51 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] What is *wrong* with this query???

 

 

I give! I'm flummoxed!

 

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  ****** <-- LEFT JOIN  “ON WHAT?”

    where

        schedule.status = 'active' and company.status = 'active' and bld.status = 'active';

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

From
Adrian Klaver
Date:
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.klaver@gmail.com

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

From
Rodrigo Gonzalez
Date:
El 05/11/11 01:51, Steve Murphy escribió:

 

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?

 

 

The join condition maybe?

left join (select....)  as bid on ..something....

            


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

From
Tair Sabirgaliev
Date:
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

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

From
Raymond O'Donnell
Date:
On 05/11/2011 04:51, Steve Murphy wrote:
> 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';

You're missing the "on" bit after the join, and I think an alias for the
inline view also:

...left join (....) x on (schedule.whatever = x.whatever)

BTW it's a good idea to use explicit column names, not "select *" -
makes for easier bug-finding.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

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

From
Antonio Goméz Soto
Date:
Steve,

Op 05-11-11 05:51, Steve Murphy schreef:
> 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 think you mean every company has three buildings...


> 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?
>


I think you actually want to do this:

update schedule set building = (select id from building where company = schedule.company order by id limit 1);

Best,
Antonio.