MySQL query question

I have a hierarchical menu table that uses an id and parentid fields to relate and create the tiered structure which never runs more than 4 layers deep. The first two records in the table are identical except that record #2 shows #1 as the parentid and record #1 shows null for the parentid. Again, the rest of the field values are identical for those two records.

When I run the following query with 2 as the id (prodcatid) it all works fine. I get the structure built and all the ‘pagecall’ values are returned as expected. But when I run it with 1 as the id (prodcatid), it returns an empty set. I have no idea why so if someone much more intelligent than I can give me a push in the right direction that would be very much appreciated. Thanks

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 = 2 or l1.prodcatid = 2) 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 ((l2.prodcatparent = 2 or l2.prodcatid = 2) and l2.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 ((l3.prodcatparent = 2 or l3.prodcatid = 2) and l3.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 ((l4.prodcatparent = 2 or l4.prodcatid = 2) and l4.orgcode = 1 and l4.pagecall IS NOT NULL) and (l1.prodcattypeid = 3 or l2.prodcattypeid = 3 or l3.prodcattypeid = 3 or l4.prodcattypeid = 3)

Disregard. I figured it out. The prodcatparent and id always need to be l1.

1 Like

Well done :slight_smile:

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