Professional Web Applications Themes

Joining Multiple Tables - Coldfusion Database Access

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 = ...

  1. #1

    Default Joining Multiple Tables

    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

    kljkhkjhkjh Guest

  2. #2

    Default Re: Joining Multiple Tables

    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



    paross1 Guest

  3. #3

    Default Re: Joining Multiple Tables

    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

    kljkhkjhkjh Guest

  4. #4

    Default Re: Joining Multiple Tables

    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 Guest

  5. #5

    Default Re: Joining Multiple Tables

    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

    paross1 Guest

  6. #6

    Default Re: Joining Multiple Tables

    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

    kljkhkjhkjh Guest

  7. #7

    Default Re: Joining Multiple Tables

    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>

    paross1 Guest

  8. #8

    Default Re: Joining Multiple Tables

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

    Mark
    kljkhkjhkjh Guest

Similar Threads

  1. Joining three tables
    By DettCom in forum Coldfusion Database Access
    Replies: 4
    Last Post: July 19th, 06:12 PM
  2. Joining tables in cfquery
    By phamtum in forum Coldfusion Database Access
    Replies: 7
    Last Post: June 7th, 09:11 PM
  3. Joining tables, Please help
    By Student_bob in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 19th, 08:49 AM
  4. joining 3 tables?
    By siti_nana in forum Dreamweaver AppDev
    Replies: 2
    Last Post: February 27th, 08:16 AM
  5. joining 3 tables in dataset
    By Artur Niesporek in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: April 9th, 01:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139