List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:November 10 2003 5:56am
Subject:Complex query woes
View as plain text  
I am hoping someone here might have an idea how to set up a complex query I
have been trying to figure out.

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.

Is there any way around this with a single query instead of multiple queries
in MySQL?

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


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