Thanks Shannon. :)
-----Original Message----- From: Shannon Wade [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 4:04 PM To: Jim MacDiarmid Subject: Re: Joining a table to itself not sure i understand the order of your table joining i just reordered them and it works. 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 cats3 ON cats2.ParentId = cats2.catid On Jul 8, 2008, at 3:45 PM, Jim MacDiarmid 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 > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
