Posted by Bonge Boo! on 09/10/05 13:46

On 10/9/05 10:43, in article BF48696D.AD215%bingbong@spamcop.net, "Bonge
Boo!" <bingbong@spamcop.net> wrote:

> The only thing I can found like this is to store the results of the first
> query in a temporary table and then join that to to the 2nd query.
>
> Is that sensible or am I missing a trick?

I hope I can answer this question myself now. Assuming I'm not missing
another obvious way of doing it. My SQL is below

## make a temporary table to store the results of the first query in
## Note!! Don't put a ; on the the end of the CREATE TEMPORARY TABLE query
## You make a blank table!
##This method copies the result of the SELECT into the temp table

CREATE TEMPORARY TABLE IF NOT EXISTS summarised

SELECT
campaign_details.datasource,
campaign_details.supplier AS Supplier,
campaign_details.type AS 'Type',
campaign_details.datasource_name AS 'Campaign Name',
campaign_details.link AS 'Link',
campaign_details.campaign AS 'Campaign',
count(april_tracking.datasource) AS 'Unique Vistors'
FROM campaign_details LEFT JOIN april_tracking USING (datasource)
WHERE campaign_details.campaign = 'april05'
GROUP BY campaign_details.datasource;

## Join our second query to results gather from the first table
SELECT
summarised.*,
count(april_hits.datasource) AS 'Total Visits'
FROM summarised LEFT JOIN april_hits USING (datasource)
GROUP BY summarised.datasource;

Hope that might be vaguely of interest to someone out there, and many thanks
to Hilarion!

[Back to original message]