Joining Multiple Tables

[kljkhkjhkjh]

I have a query that enables me to join various tables together. I managed to
get all the data I need from all of the tables, aside from one, apart from the
DISTINCT impressions.imp_source . I also need a COUNT of the
impressions.imp_source. If for example DISTINCT impressions.imp_source returned
the numbers, 1,3,5,8... I would like to know how many records are in the
1,3,5,8,... so there might be 10 x 1's ... and 5 x 3's ... and 15 x 5's etc
This is the main query: <CFQUERY name='get_imp' datasource='*****'> SELECT
DISTINCT
impressions.imp_source,campaigns.camp_title,source .source_width,source.source_he
ight FROM impressions,source,campaigns WHERE impressions.imp_source =
source.source_UID AND campaigns.camp_UID = source.source_camp_UID AND
imp_aff_UID IN (#valuelist(get_aff.aff_UID)#) AND imp_date BETWEEN
#createODBCdate(form_from)# AND #createODBCdate(form_to)# ORDER BY
campaigns.camp_title </CFQUERY> Here is a LOOP that I set up to return all of
that information, obviously this is very inefficient as it has to loop and
requery for every distinct row.. I need this building into the query above.
<Cfoutput QUERY='get_imp'> <CFQUERY name='get_imp_total' datasource='*****'>
SELECT COUNT (impressions.imp_source) as total_imp FROM impressions WHERE
imp_source = #get_imp.imp_source# AND imp_aff_UID IN
(#valuelist(get_aff.aff_UID)#) AND imp_date BETWEEN
#createODBCdate(form_from)# AND #createODBCdate(form_to)# </CFQUERY>
#get_imp.camp_title#,#get_imp.imp_source#,#get_imp .source_width# x
#get_imp.source_height#, Total: #get_imp_total.total_imp#<br> </CFoutput>
Appreciate any help Mark

[paross1]

I believe what you want is a matter of selecting impressions.imp_source and a
count(*) and doing a GROUP BY.

<CFQUERY name="get_imp_total" datasource="*****">
SELECT impressions.imp_source, COUNT(*) as total_imp
FROM impressions
WHERE imp_aff_UID IN (#valuelist(get_aff.aff_UID)#)
AND imp_date BETWEEN #createODBCdate(form_from)# AND #createODBCdate(form_to)#
GROUP BY impressions.imp_source
ORDER BY impressions.imp_source
</CFQUERY>

Phil

[kljkhkjhkjh]

Thanks Phil, That does indeed give the correct numbers. However this is only
requesting data from the impressions table, I tried to put the join back in so
I can combine the rest of the data as in the original query, but I get
aggregate errors in the grouping :( Mark

[paross1]

You can't really include columns from all of the tables in the same query
because then you would have to group by all of the columns (except the count),
and then it would be counting distinct combinations of all columns selected.

Phil

[paross1]

I think that this query would give you everything in a single query, but you
will have redundancy where you will get the same total_imp count fore every row
with the same imp_source, but you could "filter" that in the output.

<CFQUERY name="get_imp" datasource="*****">
SELECT DISTINCT i.imp_source,
(SELECT COUNT(*)
FROM impressions i1
WHERE i.imp_source = i1.imp_source) AS total_imp,
c.camp_title,
s.source_width,
s.source_height
FROM impressions i, source s, campaigns c
WHERE i.imp_source = s.source_UID
AND c.camp_UID = s.source_camp_UID
AND i.imp_aff_UID IN (#valuelist(get_aff.aff_UID)#)
AND i.imp_date BETWEEN #createODBCdate(form_from)# AND
#createODBCdate(form_to)#
ORDER BY c.camp_title
</CFQUERY>

Phil

[kljkhkjhkjh]

Thanks.. Gave it a shot.. I see what you mean, it's basically ignoring the
imp_aff_UID part of the WHERE, so it's returning incorrect counts. No matter
what the imp_aff_UID is, it ignores my filter in the query and gives me a total
number for all records regardless.. which is a problem :( Mark

[paross1]

Does this work any better?

<CFQUERY name="get_imp" datasource="*****">
SELECT DISTINCT i.imp_source,
(SELECT COUNT(*)
FROM impressions i1
WHERE i.imp_source = i1.imp_source
AND i1.imp_source = s.source_UID
AND i1.imp_aff_UID = i.imp_aff_UID
AND i1.imp_date = i.imp_date) AS total_imp,
c.camp_title,
s.source_width,
s.source_height
FROM impressions i, source s, campaigns c
WHERE i.imp_source = s.source_UID
AND c.camp_UID = s.source_camp_UID
AND i.imp_aff_UID IN (#valuelist(get_aff.aff_UID)#)
AND i.imp_date BETWEEN #createODBCdate(form_from)# AND
#createODBCdate(form_to)#
ORDER BY c.camp_title
</CFQUERY>

[kljkhkjhkjh]

Nope.. It gives too many records., seems to give DISTINCTS for all records, plus all the total_imp's are all '1'

Mark