MySQL Menu Structure Query

I am using a table with the followiing definition to create a 4 tiered menu structure. Each bottom level row is a ‘prodcattypeid’=3 and contains a number in the ‘pagecall’ column. What I want to get is a distinct list of pagecall values for the bottom level rows given a specific top level row, which in this case is ‘l1.prodcatparent’=1.

CREATE  TABLE IF NOT EXISTS `prodcat` (
  `prodcatid` INT NOT NULL ,
  `prodcatparent` INT NULL ,
  `orgcode` SMALLINT NOT NULL ,
  `prodcatname` VARCHAR(145) NOT NULL ,
  `displayname` VARCHAR(75) NULL ,
  `sortorder` SMALLINT NULL ,
  `prodcattypeid` TINYINT NULL ,
  `prodcatstatusid` TINYINT NULL ,
  `pagecall` VARCHAR(45) NULL ,
  `dateadded` DATETIME NULL ,
  `addedby` INT NULL ,
  `lastupdate` DATETIME NULL ,
  `updateby` INT NULL ,
  PRIMARY KEY (`prodcatid`) )

If I run the following query I get the list of pagecalls that I am looking for but they are in 4 columns because of the l1 - l4 joins.

select l1.pagecall, l2.pagecall, l3.pagecall, l4.pagecall
		from prodcat as l1
		left outer join prodcat as l2 on l2.prodcatparent = l1.prodcatid
		left outer join prodcat as l3 on l3.prodcatparent = l2.prodcatid
		left outer join prodcat as l4 on l4.prodcatparent = l3.prodcatid
		where (l1.prodcatparent = 1 and l1.orgcode = 1) and (l1.prodcattypeid = 3 or l2.prodcattypeid = 3 or l3.prodcattypeid = 3 or l4.prodcattypeid = 3)

I want to use the resultant set of pagecall numbers as a part of a “where x in (select…)” and this is the select statement that returns my comma delimited list of numbers.

Not sure if this can be done in a single query but want to exhaust that possibility before going to other methods so I would welcome any input that you might have. Thanks

Quick update, when I run the query now as

select id from table where tableid in (select...above query)

I get “#1241 - Operand should contain 1 column(s)”

[quote=“bostboy, post:2, topic:111075, full:true”]I get “#1241 - Operand should contain 1 column(s)”
[/quote]let’s start with this problem first

you’re getting the error because you’re asking for tableid to be in a list of values – consequently, that subquery can return only one column, the values that you want tableid to be found in

once you’ve understood this, your next step is to decide how you want to collapse that hierarchy into one resulting column (hint: UNION)

Yep, was just going to post that. This works

select l1.pagecall
		from prodcat as l1
		left outer join prodcat as l2 on l2.prodcatparent = l1.prodcatid
		left outer join prodcat as l3 on l3.prodcatparent = l2.prodcatid
		left outer join prodcat as l4 on l4.prodcatparent = l3.prodcatid
		where (l1.prodcatparent = 1 and l1.orgcode = 1 and l1.pagecall IS NOT NULL) and (l1.prodcattypeid = 3 or l2.prodcattypeid = 3 or l3.prodcattypeid = 3 or l4.prodcattypeid = 3)
union select l2.pagecall
		from prodcat as l1
		left outer join prodcat as l2 on l2.prodcatparent = l1.prodcatid
		left outer join prodcat as l3 on l3.prodcatparent = l2.prodcatid
		left outer join prodcat as l4 on l4.prodcatparent = l3.prodcatid
		where (l1.prodcatparent = 1 and l1.orgcode = 1 and l2.pagecall IS NOT NULL) and (l1.prodcattypeid = 3 or l2.prodcattypeid = 3 or l3.prodcattypeid = 3 or l4.prodcattypeid = 3)
union select l3.pagecall
		from prodcat as l1
		left outer join prodcat as l2 on l2.prodcatparent = l1.prodcatid
		left outer join prodcat as l3 on l3.prodcatparent = l2.prodcatid
		left outer join prodcat as l4 on l4.prodcatparent = l3.prodcatid
		where (l1.prodcatparent = 1 and l1.orgcode = 1 and l3.pagecall IS NOT NULL) and (l1.prodcattypeid = 3 or l2.prodcattypeid = 3 or l3.prodcattypeid = 3 or l4.prodcattypeid = 3)
union select l4.pagecall
		from prodcat as l1
		left outer join prodcat as l2 on l2.prodcatparent = l1.prodcatid
		left outer join prodcat as l3 on l3.prodcatparent = l2.prodcatid
		left outer join prodcat as l4 on l4.prodcatparent = l3.prodcatid
		where (l1.prodcatparent = 1 and l1.orgcode = 1 and l4.pagecall IS NOT NULL) and (l1.prodcattypeid = 3 or l2.prodcattypeid = 3 or l3.prodcattypeid = 3 or l4.prodcattypeid = 3)

Thanks for the heads up in any event.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.