Re: What is wrong with this query???

Rodrigo Gonzalez <rjgonzale@xxxxxxxxxxxxxx> · Sat, 05 Nov 2011 16:17:13 -0300



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

Re: What is wrong with this query???