[ cf-dev ] Confusing SQL

[Dave Phipps]

Hi,

I have three queries that build a navigation system which is running really slowly. This is because I haven't been able to combine the three queries into one and therefore have to do nested loops which results in about 50 queries running for some sections of the site.

The three queries are below:

ListCircuits:

SELECT circuits.circuitid, circuits.circuitname, circuits.contentid, circuits.displayname, content.contentid, content.pagetitle, content.contentdate, content.contentdescription, content.lastupdate, content.app_status, content.lastupdate, groups.groupid, groups.groupname
FROM groups, content_group, circuits LEFT JOIN content ON circuits.contentid = content.contentid
WHERE content.contentid = content_group.contentid
AND groups.groupid = content_group.groupid
ORDER BY circuits.circuitid

ListMenus

SELECT circuits.circuitid, circuits.circuitname, circuits.displayname, menus.menuid, menus.menuname, menus.contentid, submenus.submenuid, content.pagetitle, content.contentdate, content.contentdescription, content.lastupdate, content.app_status, groups.groupid, groups.groupname
FROM groups, circuits LEFT JOIN menus ON circuits.circuitid = menus.circuitid LEFT JOIN submenus ON menus.menuid = submenus.menuid LEFT JOIN content ON menus.contentid = content.contentid LEFT JOIN content_group ON content.contentid = content_group.contentid
WHERE content_group.groupid = groups.groupid
AND circuits.circuitid = #listCircuits.circuitid#
AND content.pagetitle != 'The Week Ahead'
GROUP BY content.contentid
ORDER BY menus.menuid

ListContent

SELECT submenus.submenuid, submenus.menuid, submenus.submenuname, content.contentid, content.pagetitle, content.contentdate, content.contentdescription, content.lastupdate, content.app_status, content.isDeleted, groups.groupname, groups.groupid
FROM submenus LEFT JOIN content ON submenus.submenuid = content.submenuid LEFT JOIN content_group ON content.contentid = content_group.contentid LEFT JOIN groups ON content_group.groupid = groups.groupid
WHERE (submenus.menuid = #list_menus.menuid#)
ORDER BY submenus.submenuid, content.contentid

Can anyone see a way to pool these three queries into one query? The problem I have found is that each table (circuits, menus, submenus) has a contentid (which is the default page for that section).

Any help on this?

Cheers

Dave



--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]