List:General Discussion« Previous MessageNext Message »
From:Leo Date:November 10 2003 6:17am
Subject:Re: Complex query woes
View as plain text  
have you try left join?

customers mgr
left join customers building on
left join costumers tenant on
group by,,
order by, building.bldgname,

hopefully it work :)


  ----- Original Message ----- 
  From: Steffan A. Cline 
  To: mysql@stripped 
  Sent: Monday, November 10, 2003 12:56 PM
  Subject: Complex query woes

  Basically I have a table that contains 3 types of records. Property
  managers, buildings and tenants. They are related upon insert by an ID and a
  PID (parent id). For example :

  ID  PID     Category    Name
  1           PM          ABC Management
  2   1       Bldg        Glen Heights
  3   2       tenant      Joe's salon

  Hopefully this shows how they are related. My goal is to ultimately on a
  Lasso (like php) page to render them like this :

  ABC Management
      Glen Heights
          Joe's salon
      Some other building
          Some other tenant

  I am able to handle the formatting fine the issue is how to get the data
  returned like this. I tried the following :

  select, building.bldgname, from customers
  as mgr,customers as building, customers as tenant where = and  =  order by,building.bldgname,;

  But it only returns 173 rows are there are 279. As you will see in
  there are some property managers with buildings and no tenants, also
  property manager with no buildings. These get omitted by the above sql.
  Currently I am doing this with nested statements via lasso but is getting
  ridiculously slow on the live system as they add more and more clients.

Complex query woesSteffan A. Cline10 Nov
  • Re: Complex query woesLeo10 Nov
    • Subqueries in version 4.1Wouter Coppieters10 Nov
      • Re: Subqueries in version 4.1Egor Egorov10 Nov
    • Re: Complex query woesSteffan A. Cline10 Nov
      • query problemBusiness A2Z10 Nov
      • Re: Complex query woesLeo11 Nov