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: [EMAIL PROTECTED]
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.
