List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:November 10 2003 1:12pm
Subject:Re: Complex query woes
View as plain text  
Leo,

    Thanks for the quick reply. There was a typo but I fixed it. Below is
what I used after correcting it :

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

This was closer. Problem now is that it took 6.56 seconds and returned 610
rows. I have no idea how I now have 610 rows where there are only 279. Any
thing else you would suggest?




Thanks

Steffan

---------------------------------------------------------------
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
Steffan@stripped                             Phoenix, Az
http://www.ExecuChoice.net                                  USA
AIM : SteffanC          ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
                                  Lasso Partner Alliance Member
---------------------------------------------------------------


> From: "Leo" <leo@stripped>
> Date: Mon, 10 Nov 2003 13:17:50 +0700
> To: "Steffan A. Cline" <steffan@stripped>, <mysql@stripped>
> Subject: Re: Complex query woes
> 
> 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