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
[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
---------------------------------------------------------------
> From: "Leo" <[EMAIL PROTECTED]>
> Date: Mon, 10 Nov 2003 13:17:50 +0700
> To: "Steffan A. Cline" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
> 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: [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.
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
