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

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



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                                  USA
AIM : SteffanC          ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
                                  Lasso Partner Alliance Member

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