I think you have just got your table names confused. Try this one SELECT
cats.CatId, cats.cat As cat, cats1.catid AS catid1, cats1.cat As cat1, cats2.catid AS catid2, cats2.cat AS cat2, cats3.catid AS catid3, cats3.cat AS cat3 FROM vb_ldcats as cats LEFT JOIN vb_ldcats As cats1 ON cats1.ParentId = cats.catid LEFT JOIN vb_ldcats As cats2 ON cats2.ParentId = cats1.catid LEFT JOIN vb_ldcats As cats3 ON cats3.ParentId = cats2.catid Phil On Tue, Jul 8, 2008 at 3:45 PM, Jim MacDiarmid <[EMAIL PROTECTED]> wrote: > I'm hoping someone can help me with this. I have a table of categories > that > I'm trying to join to itself, but I keep getting the error "unknown column: > Cats1.parentid in on clause". > > > > > > Here is the SQL for the table: > > CREATE TABLE `vb_ldcats` ( > > `catid` int(10) AUTO_INCREMENT NOT NULL, > > `cattypeid` int(10) NOT NULL, > > `cat` varchar(50), > > `parentid` int, > > `relatedid` int, > > `description` text, > > /* Keys */ > > PRIMARY KEY (`catid`) > > ) ENGINE = MyISAM; > > > > > > And here is the query: > > > > SELECT > > cats.CatId, > > cats.cat As cat, > > cats1.catid AS catid1, > > cats1.cat As cat1, > > cats2.catid AS catid2, > > cats2.cat AS cat2, > > cats3.catid AS catid3, > > cats3.cat AS cat3 > > FROM > > vb_ldcats as cats3 > > LEFT JOIN vb_ldcats As cats ON cats1.ParentId = cats.catid > > LEFT JOIN vb_ldcats As cats1 ON cats2.ParentId = cats1.catid > > LEFT JOIN vb_ldcats As cats2 ON cats3.ParentId = cats2.catid > > > > Thanks in advance! J > > > > Jim > > > > -- Help build our city at http://free-dc.myminicity.com !
