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
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
Lasso Partner Alliance Member
---------------------------------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
