Re: Strange mysql/pear db self join help needed.

> Can someone answer a MySQL question for me? I'm trying to do a self join on > my category table and I'm only coming up with 2 fields in my recordset when > I know I should have 5 fields. Here is the code I'm using. > > >SELECT Cats3.CatId, Cats.Cat, Cats1.Cat, Cats2.Cat, Cats3.Cat > >FROM (((Cats As Cats3 left JOIN Cats As Cats2 ON Cats3.ParentId = > Cats2.CatId) > >LEFT JOIN Cats As Cats1 ON Cats2.ParentId = Cats1.CatId) > >LEFT JOIN Cats As Cats ON Cats1.ParentId = Cats.CatId) > >WHERE Cats3.CatTypeId = 2 AND Cats.ParentId is NULL > >ORDER BY (Cats.Cat + Cats1.Cat + Cats2.Cat + Cats3.Cat) > > > When I execute this query in something like Navcat 2005 or SQLyog it works > fine. By the way, I'm also using the PEAR DB class. > > Any ideas? > > Thanks :) > > Jim MacDiarmid As a total guess, bearing in mind I have never used PEAR, could it be because you only have two different field names (Cat and CatId)? If you try aliasing them like this, does it work? SELECT Cats3.CatId AS Cats3ID, Cats.Cat AS CatsCat, Cats1.Cat AS Cats1Cat, Cats2.Cat AS Cats2Cat, Cats3.Cat AS Cats3Cat It's hard to say without some example of how you are accessing it in PHP. If, as you say, it works OK directly in mySQL, the problem may well be with the PHP code.