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
[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)
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)