What is *wrong* with this query???

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.

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';

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

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

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

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

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.