Обсуждение: What is *wrong* with this query???
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 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
The join condition maybe?
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....) 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.