[GENERAL] What is *wrong* with this query???
I give! I'm flummoxed! Here is what I have, 3 tables: schedule companybuilding 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 Iactive 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: [GENERAL] What is *wrong* with this query???
See embedded note after “as bld” Dave From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@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: [GENERAL] 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 companybuilding 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 Iactive 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is *wrong* with this query???
On Fri, Nov 4, 2011 at 10:51 PM, Steve Murphy smur...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is *wrong* with this query???
On Sat, Nov 5, 2011 at 10:51 AM, Steve Murphy smur...@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 Республика Казахстан, 01 г.Астана, ул.Сарайшык 34, ВП-27 Тел.: +7 (7172) 56-89-31 Сот.: +7 (702) 2173359 e-mail: tair.sabirgal...@bee.kz Tair Sabirgaliev BEE Software Ltd. Republic of Kazakhstan, 01 Astana, Sarayshyk str. 34, sect. 27 Tel.: +7 (7172) 56-89-31 Mob.: +7 (702) 2173359 e-mail: tair.sabirgal...@bee.kz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is *wrong* with this query???
El 05/11/11 01:51, Steve Murphy escribió: I give! I'm flummoxed! Here is what I have, 3 tables: schedule companybuilding 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 Iactive 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: [GENERAL] What is *wrong* with this query???
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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is *wrong* with this query???
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
