[SQLObject] Complex query

[Sybren Stüvel]

Hi folks,

I'm trying to build a rather complex query using SQLBuilder. The
query: I want all Zone IDs for which there is a subzone with a
registered user in it.

This is the query in regular SQL:

   select z.id
   from zone z
   left join subzone s on(z.id = s.zone_id)
   where s.id in (
         select s.id from subzone s
         left join registered_user r on(
             s.start_x <= r.location_x and
             s.end_x >= r.location_x and
             s.start_y <= r.location_y and
             s.end_y >= r.location_y and
             s.start_z <= r.location_z and
             s.end_z >= r.location_z)
         group by s.id
         having count(r.id) > 0
     )

The relevant parts of the table definition:

zone
    id

subzone
    id
    zone_id
    start_x
    start_y
    start_z
    end_x
    end_y
    end_z

registered_user
    location_x
    location_y
    location_z

Can you folks give me any hints? I tried using SQLObject, but I can't
wrap my mind around it...

-- 
Sybren Stüvel

"Gravitation cannot be held responsible for people falling in love."
-- Albert Einstein

signature.asc
Description: Digital signature