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?

select 
mgr.company,
building.bldgname,
tenant.id
from
customers mgr
left join customers building on building.pid=mgr.id
left join costumers tenant on tenant.pid=building.id
group by mgr.id, building.id, tenant.id
order by mgr.company, building.bldgname, tenant.company

hopefully it work :)

-leo-

  ----- 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 mgr.company, building.bldgname, tenant.company from customers
  as mgr,customers as building, customers as tenant where building.pid =
  mgr.id and tenant.pid  = building.id  order by
  mgr.company,building.bldgname,tenant.company;

  But it only returns 173 rows are there are 279. As you will see in
  http://phattwelve.hldns.com:90/workorder/findaccount2.lasso
  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.



Thread
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