I’m trying to combine some queries on a web page and I’m having unexpected results.
We’re using a home-built CMS that manages our website, and it holds things like the page title and the contact person for that page. Our website is broken down into topic areas, so we have a summary page that shows all the topics listed in a table, along with the default contact person for that topic. It also lists total pages per topic, and pages with no contact or not title.
The summary page is built using 4 queries. One to list out the topics with their default contact, and three more to do the total pages, the pages with no contact, and the pages with no title.
Here are the queries from the old, working version of the page:
Main query to get topics and contacts:
SELECT default_contacts.topic_id
, default_contacts.topic_name
, default_contacts.contact_id
, maindir.first_name & ' ' & maindir.last_name AS fullname
, pagecontacts.page_topic
, pagecontacts.page_id
, pagecontacts.page_title
, pagecontacts.contact_id
FROM ( ( default_contacts
LEFT JOIN maindir
ON maindir.main_id = default_contacts.contact_id )
LEFT JOIN pagecontacts
ON default_contacts.topic_name = pagecontacts.page_topic )
ORDER BY topic_name
Then these three to get the totals
Total pages in topic:
SELECT COUNT(page_id) as a
FROM pagecontacts
WHERE page_topic = '#gettopics.page_topic#'
Pages with no contact:
SELECT COUNT(page_id) as b
FROM pagecontacts
WHERE page_topic = '#gettopics.page_topic#'
AND contact_id = 0
Pages with no title:
SELECT COUNT(page_id) as c
FROM pagecontacts
WHERE page_topic = '#gettopics.page_topic#'
AND ( page_title IS NULL
OR page_title = '' )
Sine we’re stuck with MS Access, I’ve been trying to figure out a way to streamline pages. So one of the things I thought of was to combine the above four queries into one. Well, sort of one. I still have the same number of select statements, so it might not be ideal. This is what I came up with:
SELECT default_contacts.topic_id
, default_contacts.topic_name
, default_contacts.contact_id
, maindir.first_name & ' ' & maindir.last_name AS fullname
, pagecontacts.page_topic
, pagecontacts.page_id
, pagecontacts.page_title
, pagecontacts.contact_id
, ( SELECT COUNT(page_id)
FROM pagecontacts
WHERE page_topic = topic_name ) as a
, ( SELECT COUNT(page_id)
FROM pagecontacts
WHERE page_topic = topic_name
AND contact_id = 0 ) as b
, ( SELECT COUNT(page_id)
FROM pagecontacts
WHERE page_topic = topic_name
AND ( page_title IS NULL
OR page_title = '' ) ) as c
FROM ( ( default_contacts
LEFT JOIN maindir
ON maindir.main_id = default_contacts.contact_id )
LEFT JOIN pagecontacts
ON default_contacts.topic_name = pagecontacts.page_topic )
ORDER BY topic_name
When I run this on my local machine, it takes waaaaaay longer than the four separate queries, and when I copy it to our test server, the query times out. I would expect that at worse, it would perform identical to the separate ones and not worse.
What am I doing wrong?