2nd try, left join

Discussion in 'ASP General' started by Jeff Uchtman, Apr 8, 2004.

  1. Jeff Uchtman

    Jeff Uchtman Guest

    This SQL statement below works wonderful for counting one instance, how can
    I count the other 3 on the same table in the same statement?. As listed
    below it is counting PetACirc. I also have PetBCirc to count as BCount,
    PetCCirc to count as CCount, and PetDCirc as DCount. Same instance of
    Cir_ID, just in a different column. .

    SELECT p.Cir_ID, p.First_Name, p.Last_Name,
    COUNT(a.PetACirc) AS ACount
    FROM CirID p
    LEFT OUTER JOIN Verified a ON p.Cir_ID = a.PetACirc
    GROUP BY p.Cir_ID, p.First_Name, p.Last_Name
    ORDER BY p.Cir_ID

    Any help and guidance is greatly appreciated!

    Jeff
     
    Jeff Uchtman, Apr 8, 2004
    #1
    1. Advertising

  2. It's late. I am tired, and I am not testing any of this. But, here is the
    basic idea (may actually work, but probably needs tweaking):

    SELECT p.Cir_ID, p.First_Name, p.Last_Name,
    COUNT(a.PetACirc) AS ACount,
    COUNT(b.PetACirc) AS BCount,
    COUNT(c.PetACirc) AS CCount
    FROM CirID p
    LEFT OUTER JOIN Verified a ON p.Cir_ID = a.PetACirc
    LEFT OUTER JOIN Verified b ON p.Cir_ID = a.PetBCirc
    LEFT OUTER JOIN Verified c ON p.Cir_ID = a.PetCCirc
    GROUP BY p.Cir_ID, p.First_Name, p.Last_Name
    ORDER BY p.Cir_ID

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    ***************************************************************
    Think outside the box!
    ***************************************************************
    "Jeff Uchtman" <> wrote in message
    news:...
    > This SQL statement below works wonderful for counting one instance, how

    can
    > I count the other 3 on the same table in the same statement?. As listed
    > below it is counting PetACirc. I also have PetBCirc to count as BCount,
    > PetCCirc to count as CCount, and PetDCirc as DCount. Same instance of
    > Cir_ID, just in a different column. .
    >
    > SELECT p.Cir_ID, p.First_Name, p.Last_Name,
    > COUNT(a.PetACirc) AS ACount
    > FROM CirID p
    > LEFT OUTER JOIN Verified a ON p.Cir_ID = a.PetACirc
    > GROUP BY p.Cir_ID, p.First_Name, p.Last_Name
    > ORDER BY p.Cir_ID
    >
    > Any help and guidance is greatly appreciated!
    >
    > Jeff
    >
    >
     
    Cowboy \(Gregory A. Beamer\) [MVP], Apr 8, 2004
    #2
    1. Advertising

  3. Jeff Uchtman

    Jeff Uchtman Guest

    Thanks Greg,
    Been down this road. For some reason it loops and displays a count that
    go's into the millions. I'm still learning so I am not sure why.

    Thanks again
    Jeff


    "Cowboy (Gregory A. Beamer) [MVP]" <> wrote
    in message news:ek$...
    > It's late. I am tired, and I am not testing any of this. But, here is the
    > basic idea (may actually work, but probably needs tweaking):
    >
    > SELECT p.Cir_ID, p.First_Name, p.Last_Name,
    > COUNT(a.PetACirc) AS ACount,
    > COUNT(b.PetACirc) AS BCount,
    > COUNT(c.PetACirc) AS CCount
    > FROM CirID p
    > LEFT OUTER JOIN Verified a ON p.Cir_ID = a.PetACirc
    > LEFT OUTER JOIN Verified b ON p.Cir_ID = a.PetBCirc
    > LEFT OUTER JOIN Verified c ON p.Cir_ID = a.PetCCirc
    > GROUP BY p.Cir_ID, p.First_Name, p.Last_Name
    > ORDER BY p.Cir_ID
    >
    > --
    > Gregory A. Beamer
    > MVP; MCP: +I, SE, SD, DBA
    >
    > ***************************************************************
    > Think outside the box!
    > ***************************************************************
    > "Jeff Uchtman" <> wrote in message
    > news:...
    > > This SQL statement below works wonderful for counting one instance, how

    > can
    > > I count the other 3 on the same table in the same statement?. As listed
    > > below it is counting PetACirc. I also have PetBCirc to count as BCount,
    > > PetCCirc to count as CCount, and PetDCirc as DCount. Same instance of
    > > Cir_ID, just in a different column. .
    > >
    > > SELECT p.Cir_ID, p.First_Name, p.Last_Name,
    > > COUNT(a.PetACirc) AS ACount
    > > FROM CirID p
    > > LEFT OUTER JOIN Verified a ON p.Cir_ID = a.PetACirc
    > > GROUP BY p.Cir_ID, p.First_Name, p.Last_Name
    > > ORDER BY p.Cir_ID
    > >
    > > Any help and guidance is greatly appreciated!
    > >
    > > Jeff
    > >
    > >

    >
    >
     
    Jeff Uchtman, Apr 8, 2004
    #3
  4. Jeff Uchtman

    Bob Barrows Guest

    Jeff Uchtman wrote:
    > Thanks Greg,
    > Been down this road. For some reason it loops and displays a count
    > that go's into the millions. I'm still learning so I am not sure why.
    >


    You're going to need to provide some sample data and desired results so we
    can test our solutions before providing them to you.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Apr 8, 2004
    #4
  5. Jeff Uchtman

    Jeff Uchtman Guest

    Thanks Bob,
    I have two tables, the verifed table and the CirID table.
    The CirID table holds the list of Cir_ID, First_Name, Last_Name,
    Address, ect. The Verifed table holds Verifed date input by the names
    listed in the CirID table. Upon verification, ASP stamps the Verified
    table wiht the Cir_ID in 4 possible columns, PetACirc, PetBCirc,
    PetCCirc, and PetDCirc. I would like to count the number of occurances
    in the Verified Columns, PetACic, PetBCirc, PetCCirc, and PetDCirc that
    each Cir_ID shows up. After the count I would like to associate the
    count via Cir_ID to the First_Name, Last_Name in the CirID table.

    Verified
    -----
    PetACirc PetBCirc PetCCirc PetDCirc
    | | | |
    -----------------------------
    |
    CirID |
    -------- |
    Cir_ID Last_Name First_Name

    Count number of times Cir_ID number shows up in PetACirc, PetBCirc,
    PetCCirc, PetDCirc. Display Count and join the Last_Name, First_Name to
    the count using the Cir_ID as the association.

    Hope this makes sense. It does to me but I have all the data in front
    of me. If I can explane further, let me know what I am misssing.

    Thanks
    Jeff



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Jeff Uchtman, Apr 8, 2004
    #5
  6. Jeff Uchtman wrote:

    > of me. If I can explane further, let me know what I am misssing.
    >

    All I can do is repeat my original reply:
    > You're going to need to provide some sample data and desired results
    > so we can test our solutions before providing them to you.


    OK, I'll amend the above to further explain what I am after:

    A few rows of sample data (in tabular format) and desired results from that
    sample data (also in tabular format). Only show the relevant columns.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Apr 8, 2004
    #6
  7. Jeff Uchtman

    Jeff Uchtman Guest

    Thank Bob. I hope the following is the information needed.

    Upon insertion to the verified table, the Cir_ID number is inserted into the corsponding record. I would like to count the occurances of each Cir_ID and associate then to the name of the Cir_ID listed in the Cir_ID table. The PetACirc, PetBCirc, PetCCirc, and PetDCirc my or may not have all the same Cir_ID.

    I have tried Greg's query:
    SELECT p.Cir_ID, p.First_Name, p.Last_Name,
    COUNT(a.PetACirc) AS ACount,
    COUNT(b.PetACirc) AS BCount,
    COUNT(c.PetACirc) AS CCount
    FROM CirID p
    LEFT OUTER JOIN Verified a ON p.Cir_ID = a.PetACirc
    LEFT OUTER JOIN Verified b ON p.Cir_ID = a.PetBCirc
    LEFT OUTER JOIN Verified c ON p.Cir_ID = a.PetCCirc
    GROUP BY p.Cir_ID, p.First_Name, p.Last_Name
    ORDER BY p.Cir_ID
    and it seems to loop forever and return results in the millions when the highest result doing the query on Pet column at a time is less then 15000.


    Verified
    ID Last_Name First_Name Middle_Name PetACirc PetBCirc PetCCirc PetDCirc
    1 LESSIG DONNA M 101 100 122 112
    2 ZASTROW SUE D 122 123 112 123
    3 CHRISTENSEN TODD D 122 122 115 122
    4 MCLAUGHLIN ROLAND E 102 144 122 122
    5 MCLAUGHLIN JOLENE F 122 122 122 122
    6 GREENWOOD BRANDON C 122 112 122 122
    7 ALGYA ROBERT A 122 122 169 122
    8 MILLS JON H 122 132 122 122
    9 HULTQUIST MICHAEL J 111 122 102 122
    10 SHEWAN ROBERT L 112 122 100 105




    Cir_ID

    Cir_ID
    Last_Name
    First_Name

    100
    Cornelius
    Tony

    101
    DeKeyser
    Paul

    102
    Andrews
    Mary

    103
    Backhaus
    Clint

    105
    Brummont
    Roger

    106
    Corbino
    Theresa

    107
    Cornelius
    Andrew

    108
    Cramer
    Paul

    109
    Cronin
    John



    Result Table I am after Cir_ID First_Name Last_Name ATotal BTotal CTotal DTotal
    100 Tony Cornelius 752 744 736 700
    101 Paul DeKeyser 570 560 550 515
    102 Mary Andrews 35 30 32 31
    103 Clint Backhaus 1484 1477 1466 1463
    105 Roger Brummont 546 533 544 533
    106 Theresa Corbino 1430 1420 1415 1411
    107 Andrew Cornelius 35 30 27 19
    108 Paul Cramer 28 22 25 27
    109 John Cronin 769 777 782 750
    110 Darrell Davis 0 5 0 3




    "Bob Barrows [MVP]" <> wrote in message news:%23%...
    > Jeff Uchtman wrote:
    >
    > > of me. If I can explane further, let me know what I am misssing.
    > >

    > All I can do is repeat my original reply:
    > > You're going to need to provide some sample data and desired results
    > > so we can test our solutions before providing them to you.

    >
    > OK, I'll amend the above to further explain what I am after:
    >
    > A few rows of sample data (in tabular format) and desired results from that
    > sample data (also in tabular format). Only show the relevant columns.
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    Jeff Uchtman, Apr 9, 2004
    #7
  8. Jeff Uchtman

    Bob Barrows Guest

    Jeff Uchtman wrote:
    > Thank Bob. I hope the following is the information needed.
    >

    Yes, it's what we need. I'll get back to you later.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Apr 9, 2004
    #8
  9. Jeff Uchtman

    Bob Barrows Guest

    Jeff Uchtman wrote:
    > Thank Bob. I hope the following is the information needed.
    >
    > Upon insertion to the verified table, the Cir_ID number is inserted
    > into the corsponding record. I would like to count the occurances of
    > each Cir_ID and associate then to the name of the Cir_ID listed in
    > the Cir_ID table. The PetACirc, PetBCirc, PetCCirc, and PetDCirc my
    > or may not have all the same Cir_ID.
    >
    > I have tried Greg's query:
    > SELECT p.Cir_ID, p.First_Name, p.Last_Name,
    > COUNT(a.PetACirc) AS ACount,
    > COUNT(b.PetACirc) AS BCount,
    > COUNT(c.PetACirc) AS CCount
    > FROM CirID p
    > LEFT OUTER JOIN Verified a ON p.Cir_ID = a.PetACirc
    > LEFT OUTER JOIN Verified b ON p.Cir_ID = a.PetBCirc
    > LEFT OUTER JOIN Verified c ON p.Cir_ID = a.PetCCirc
    > GROUP BY p.Cir_ID, p.First_Name, p.Last_Name
    > ORDER BY p.Cir_ID
    > and it seems to loop forever and return results in the millions
    > when the highest result doing the query on Pet column at a time is
    > less then 15000.
    >
    >
    > Verified
    > ID Last_Name First_Name Middle_Name PetACirc PetBCirc
    > PetCCirc PetDCirc
    > 1 LESSIG DONNA M 101 100 122 112
    > 2 ZASTROW SUE D 122 123 112 123
    > 3 CHRISTENSEN TODD D 122 122 115 122
    > 4 MCLAUGHLIN ROLAND E 102 144 122 122
    > 5 MCLAUGHLIN JOLENE F 122 122 122 122
    > 6 GREENWOOD BRANDON C 122 112 122 122
    > 7 ALGYA ROBERT A 122 122 169 122
    > 8 MILLS JON H 122 132 122 122
    > 9 HULTQUIST MICHAEL J 111 122 102 122
    > 10 SHEWAN ROBERT L 112 122 100 105
    >
    >
    >
    >
    > Cir_ID
    >
    > Cir_ID Last_Name First_Name
    > 100 Cornelius Tony
    >
    >
    >
    >
    > Result Table I am after
    > Cir_ID First_Name Last_Name ATotal BTotal
    > CTotal DTotal
    > 100 Tony Cornelius 752 744 736 700


    ?
    Where do those numbers come from? 752? 744?

    Are these the results from the actual data? I was asking for the desired
    results from the sample data.

    Am I correct that given the sample data, the correct result for Tony
    Cornelius would be this?
    100 Tony Cornelius 0 1 1 0

    And if John Cronin's ID was 122, the result for him would be this?
    122 John Cronin 6 5 4 7

    Do you wish to show results for people who don't show up in Verified? Given
    the sample data, do you want the following rows in the result?
    103 Clint Backhaus 0 0 0 0
    etc.
    I will assume you want these rows in the result

    If the above assumptions are correct, then this query:
    SELECT Cir_ID, Last_Name, First_Name,
    (Select Count(*) FROM Verified WHERE PetACirc=c.Cir_ID) As PetACirc,
    (Select Count(*) FROM Verified WHERE PetBCirc=c.Cir_ID) As PetBCirc,
    (Select Count(*) FROM Verified WHERE PetCCirc=c.Cir_ID) As PetCCirc,
    (Select Count(*) FROM Verified WHERE PetDCirc=c.Cir_ID) As PetDCirc
    FROM CirID c
    yields these results:
    Cir_ID Last_Name First_Name PetACirc PetBCirc PetCCirc PetDCirc
    100 Cornelius Tony 0 1 1 0
    101 DeKeyser Paul 1 0 0 0
    102 Andrews Mary 1 0 1 0
    103 Backhaus Clint 0 0 0 0
    105 Brummont Roger 0 0 0 1
    106 Corbino Theresa 0 0 0 0
    107 Cornelius Andrew 0 0 0 0
    108 Cramer Paul 0 0 0 0
    122 Cronin John 6 5 5 7


    Is that what you want?

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Apr 9, 2004
    #9
  10. Jeff Uchtman

    Jeff Uchtman Guest

    Bob,
    This is perfect! Query Analyzer loves it! Thank you very much and
    thank you for making me look at the query in a different way. I never
    thought of anything but join.

    THANKS!!!!

    Jeff

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Jeff Uchtman, Apr 9, 2004
    #10
  11. Jeff Uchtman

    Bob Barrows Guest

    I've been hesitating to send this because:
    1. You seem to be happy with the first solution I posted
    and
    2. This solution seems so much more complicated than the first solution that
    it seems counter-intuititive that it would perform better. I think you
    should test both solutions to see which performs better. The first solution
    I posted requires the query engine to run 4 subqueries for every row in
    your CirID table! This is a lot of processing. This alternate solution,
    while it involves more intermediate queries, may perform better because only
    these intermediate queries are being run.

    Give it a try.
    ----------------------------------------------------------------------------
    ------
    <snip>

    If the above assumptions are correct, then let's start. The first step is to
    normalize your Verified table. It is a very bad design to have 4 PetNCirc
    columns. You should add a new table called PetCirc and insert the data as
    follows:

    ID Type CirID
    1 A 101
    1 B 100
    1 C 122
    1 D 112
    2 A 122
    2 B 123
    2 C 112
    2 D 123
    etc.

    The benefit of this design is that there is no problem adding another type.
    With your original design, you would have to modify the table design to add
    another column, and then modify all your queries to handle the new column.

    The other benefit of this design is that a simple grouping query can be used
    to get your counts:

    select CirID, Type, count(*) as Total
    From PetCirc
    Group By CirID, Type


    If you cannot change the design of the database for some reason, then you
    will
    need to create a view that will return the data in a normalized structure:

    CREATE VIEW vPetCirc AS
    SELECT ID, 'A' As Type, PetACirc As CirID FROM Verified
    UNION ALL
    SELECT ID, 'B', PetBCirc FROM Verified
    UNION ALL
    SELECT ID, 'C', PetCCirc FROM Verified
    UNION ALL
    SELECT ID, 'D' , PetDCirc FROM Verified

    Now, after running this iew creation script, you can get the raw data for yo
    ur result by joining CirID to vPetCirc:

    SELECT c.Cir_ID, c.First_Name, c.First_Name, v.Type, Count(ID) AS Total
    FROM CirID AS c LEFT JOIN vPetCirc AS v ON c.Cir_ID = v.CirID
    GROUP BY c.Cir_ID, c.First_Name, c.First_Name, v.Type;
    ORDER BY c.Cir_ID,v.Type

    This yields the following results from your sample data (after I changed
    John Cronin's DirID to 122):
    Cir_ID First_Name Last_Name Type Total
    100 Tony Cornelius B 1
    100 Tony Cornelius C 1
    101 Paul DeKeyser A 1
    102 Mary Andrews A 1
    102 Mary Andrews C 1
    103 Clint Backhaus <null> 0
    105 Roger Brummont D 1
    106 Theresa Corbino <null> 0
    107 Andrew Cornelius <null> 0
    108 Paul Cramer <null> 0
    122 John Cronin A 6
    122 John Cronin B 5
    122 John Cronin C 5
    122 John Cronin D 7


    Now you could create your desired html table by looping through this result
    in
    your vbscript code, writing the data to the appropriate columns in your html
    table, and writing zeros where there is missing data.

    Or you could do it with sql, like this:

    First, create this view:

    CREATE View vAllCirIDsAndTypes AS
    SELECT Cir_ID, 'A' As Type FROM CirID
    UNION ALL
    Select Cir_ID, 'B' FROM CirID
    UNION ALL
    Select Cir_ID, 'C' FROM CirID
    UNION ALL
    Select Cir_ID, 'D' FROM CirID

    Now, this query:
    SELECT c.Cir_ID, c.First_Name, c.Last_Name, c.Type, Count(ID) AS Total
    FROM vAllCirIDsAndTypes AS c LEFT JOIN vPetCirc AS v
    ON c.Cir_ID = v.CirID AND c.Type = v.Type
    GROUP BY c.Cir_ID, c.First_Name, c.Last_Name, c.Type

    returns these results:
    Cir_ID First_Name Last_Name Type Total
    100 Tony Cornelius A 0
    100 Tony Cornelius B 1
    100 Tony Cornelius C 1
    100 Tony Cornelius D 0
    101 Paul DeKeyser A 1
    101 Paul DeKeyser B 0
    101 Paul DeKeyser C 0
    101 Paul DeKeyser D 0
    102 Mary Andrews A 1
    102 Mary Andrews B 0
    102 Mary Andrews C 1
    102 Mary Andrews D 0
    103 Clint Backhaus A 0
    103 Clint Backhaus B 0
    103 Clint Backhaus C 0
    103 Clint Backhaus D 0
    105 Roger Brummont A 0
    105 Roger Brummont B 0
    105 Roger Brummont C 0
    105 Roger Brummont D 1
    106 Theresa Corbino A 0
    106 Theresa Corbino B 0
    106 Theresa Corbino C 0
    106 Theresa Corbino D 0
    107 Andrew Cornelius A 0
    107 Andrew Cornelius B 0
    107 Andrew Cornelius C 0
    107 Andrew Cornelius D 0
    108 Paul Cramer A 0
    108 Paul Cramer B 0
    108 Paul Cramer C 0
    108 Paul Cramer D 0
    122 John Cronin A 6
    122 John Cronin B 5
    122 John Cronin C 5
    122 John Cronin D 7

    Again, this could be your final query. You could stop at this point and run
    this query from asp, looping through the resultset and writing the data to
    the appropriate rows and columns in your html table.

    Or, you could do it with sql:

    Create a view using this script:

    CREATE VIEW vRawCounts AS
    SELECT c.Cir_ID, c.First_Name, c.Last_Name, c.Type, Count(ID) AS Total
    FROM vAllCirIDsAndTypes AS c LEFT JOIN vPetCirc AS v
    ON c.Cir_ID = v.CirID AND c.Type = v.Type
    GROUP BY c.Cir_ID, c.First_Name, c.Last_Name, c.Type

    Now we can pivot the results using this query:

    SELECT Cir_ID,First_Name,Last_Name,
    SUM(CASE Type WHEN 'A' THEN Total ELSE 0) As PetACirc,
    SUM(CASE Type WHEN 'B' THEN Total ELSE 0) As PetBCirc,
    SUM(CASE Type WHEN 'C' THEN Total ELSE 0) As PetCCirc,
    SUM(CASE Type WHEN 'D' THEN Total ELSE 0) As PetDCirc
    FROM vRawData
    GROUP BY Cir_ID,First_Name,Last_Name
    ORDER BY Cir_ID

    which yields these results:
    Query3 Cir_ID First_Name Last_Name PetACirc PetBCirc PetCCirc PetDCirc
    100 Tony Cornelius 0 1 1 0
    101 Paul DeKeyser 1 0 0 0
    102 Mary Andrews 1 0 1 0
    103 Clint Backhaus 0 0 0 0
    105 Roger Brummont 0 0 0 1
    106 Theresa Corbino 0 0 0 0
    107 Andrew Cornelius 0 0 0 0
    108 Paul Cramer 0 0 0 0
    122 John Cronin 6 5 5 7
    ----------------------------------------------------------------------------
    -
    Three views and a pivot query vs 4 queries for every row in your CirID
    table. I suspect this more complicated solution will perform better than the
    subquery approach, but, the only way to be sure is by testing both.

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Apr 11, 2004
    #11
  12. Jeff Uchtman

    Jeff Uchtman Guest

    Thanks for the great input Bob. I can't change the table design as its on a
    production model already. I did take the first code snip you sent,
    converted to stored procedure, used a getrow function, and did some table
    indexing all the find that the results were want I was after and turned
    around very quick. The 4 query on the verified table is a hot point, but at
    the present time is only reading through about 50000 results. If (when) I
    need to make this animal work again I will redesign the db to a friendly
    point for the number of PetCirc's I have. Thanks again for your great input
    and teaching me a thing or two about query points!.

    Jeff
    "Bob Barrows" <> wrote in message
    news:eB$%...
    > I've been hesitating to send this because:
    > 1. You seem to be happy with the first solution I posted
    > and
    > 2. This solution seems so much more complicated than the first solution

    that
    > it seems counter-intuititive that it would perform better. I think you
    > should test both solutions to see which performs better. The first

    solution
    > I posted requires the query engine to run 4 subqueries for every row in
    > your CirID table! This is a lot of processing. This alternate solution,
    > while it involves more intermediate queries, may perform better because

    only
    > these intermediate queries are being run.
    >
    > Give it a try.
    > --------------------------------------------------------------------------

    --
    > ------
    > <snip>
    >
    > If the above assumptions are correct, then let's start. The first step is

    to
    > normalize your Verified table. It is a very bad design to have 4 PetNCirc
    > columns. You should add a new table called PetCirc and insert the data as
    > follows:
    >
    > ID Type CirID
    > 1 A 101
    > 1 B 100
    > 1 C 122
    > 1 D 112
    > 2 A 122
    > 2 B 123
    > 2 C 112
    > 2 D 123
    > etc.
    >
    > The benefit of this design is that there is no problem adding another

    type.
    > With your original design, you would have to modify the table design to

    add
    > another column, and then modify all your queries to handle the new column.
    >
    > The other benefit of this design is that a simple grouping query can be

    used
    > to get your counts:
    >
    > select CirID, Type, count(*) as Total
    > From PetCirc
    > Group By CirID, Type
    >
    >
    > If you cannot change the design of the database for some reason, then you
    > will
    > need to create a view that will return the data in a normalized structure:
    >
    > CREATE VIEW vPetCirc AS
    > SELECT ID, 'A' As Type, PetACirc As CirID FROM Verified
    > UNION ALL
    > SELECT ID, 'B', PetBCirc FROM Verified
    > UNION ALL
    > SELECT ID, 'C', PetCCirc FROM Verified
    > UNION ALL
    > SELECT ID, 'D' , PetDCirc FROM Verified
    >
    > Now, after running this iew creation script, you can get the raw data for

    yo
    > ur result by joining CirID to vPetCirc:
    >
    > SELECT c.Cir_ID, c.First_Name, c.First_Name, v.Type, Count(ID) AS Total
    > FROM CirID AS c LEFT JOIN vPetCirc AS v ON c.Cir_ID = v.CirID
    > GROUP BY c.Cir_ID, c.First_Name, c.First_Name, v.Type;
    > ORDER BY c.Cir_ID,v.Type
    >
    > This yields the following results from your sample data (after I changed
    > John Cronin's DirID to 122):
    > Cir_ID First_Name Last_Name Type Total
    > 100 Tony Cornelius B 1
    > 100 Tony Cornelius C 1
    > 101 Paul DeKeyser A 1
    > 102 Mary Andrews A 1
    > 102 Mary Andrews C 1
    > 103 Clint Backhaus <null> 0
    > 105 Roger Brummont D 1
    > 106 Theresa Corbino <null> 0
    > 107 Andrew Cornelius <null> 0
    > 108 Paul Cramer <null> 0
    > 122 John Cronin A 6
    > 122 John Cronin B 5
    > 122 John Cronin C 5
    > 122 John Cronin D 7
    >
    >
    > Now you could create your desired html table by looping through this

    result
    > in
    > your vbscript code, writing the data to the appropriate columns in your

    html
    > table, and writing zeros where there is missing data.
    >
    > Or you could do it with sql, like this:
    >
    > First, create this view:
    >
    > CREATE View vAllCirIDsAndTypes AS
    > SELECT Cir_ID, 'A' As Type FROM CirID
    > UNION ALL
    > Select Cir_ID, 'B' FROM CirID
    > UNION ALL
    > Select Cir_ID, 'C' FROM CirID
    > UNION ALL
    > Select Cir_ID, 'D' FROM CirID
    >
    > Now, this query:
    > SELECT c.Cir_ID, c.First_Name, c.Last_Name, c.Type, Count(ID) AS Total
    > FROM vAllCirIDsAndTypes AS c LEFT JOIN vPetCirc AS v
    > ON c.Cir_ID = v.CirID AND c.Type = v.Type
    > GROUP BY c.Cir_ID, c.First_Name, c.Last_Name, c.Type
    >
    > returns these results:
    > Cir_ID First_Name Last_Name Type Total
    > 100 Tony Cornelius A 0
    > 100 Tony Cornelius B 1
    > 100 Tony Cornelius C 1
    > 100 Tony Cornelius D 0
    > 101 Paul DeKeyser A 1
    > 101 Paul DeKeyser B 0
    > 101 Paul DeKeyser C 0
    > 101 Paul DeKeyser D 0
    > 102 Mary Andrews A 1
    > 102 Mary Andrews B 0
    > 102 Mary Andrews C 1
    > 102 Mary Andrews D 0
    > 103 Clint Backhaus A 0
    > 103 Clint Backhaus B 0
    > 103 Clint Backhaus C 0
    > 103 Clint Backhaus D 0
    > 105 Roger Brummont A 0
    > 105 Roger Brummont B 0
    > 105 Roger Brummont C 0
    > 105 Roger Brummont D 1
    > 106 Theresa Corbino A 0
    > 106 Theresa Corbino B 0
    > 106 Theresa Corbino C 0
    > 106 Theresa Corbino D 0
    > 107 Andrew Cornelius A 0
    > 107 Andrew Cornelius B 0
    > 107 Andrew Cornelius C 0
    > 107 Andrew Cornelius D 0
    > 108 Paul Cramer A 0
    > 108 Paul Cramer B 0
    > 108 Paul Cramer C 0
    > 108 Paul Cramer D 0
    > 122 John Cronin A 6
    > 122 John Cronin B 5
    > 122 John Cronin C 5
    > 122 John Cronin D 7
    >
    > Again, this could be your final query. You could stop at this point and

    run
    > this query from asp, looping through the resultset and writing the data to
    > the appropriate rows and columns in your html table.
    >
    > Or, you could do it with sql:
    >
    > Create a view using this script:
    >
    > CREATE VIEW vRawCounts AS
    > SELECT c.Cir_ID, c.First_Name, c.Last_Name, c.Type, Count(ID) AS Total
    > FROM vAllCirIDsAndTypes AS c LEFT JOIN vPetCirc AS v
    > ON c.Cir_ID = v.CirID AND c.Type = v.Type
    > GROUP BY c.Cir_ID, c.First_Name, c.Last_Name, c.Type
    >
    > Now we can pivot the results using this query:
    >
    > SELECT Cir_ID,First_Name,Last_Name,
    > SUM(CASE Type WHEN 'A' THEN Total ELSE 0) As PetACirc,
    > SUM(CASE Type WHEN 'B' THEN Total ELSE 0) As PetBCirc,
    > SUM(CASE Type WHEN 'C' THEN Total ELSE 0) As PetCCirc,
    > SUM(CASE Type WHEN 'D' THEN Total ELSE 0) As PetDCirc
    > FROM vRawData
    > GROUP BY Cir_ID,First_Name,Last_Name
    > ORDER BY Cir_ID
    >
    > which yields these results:
    > Query3 Cir_ID First_Name Last_Name PetACirc PetBCirc PetCCirc PetDCirc
    > 100 Tony Cornelius 0 1 1 0
    > 101 Paul DeKeyser 1 0 0 0
    > 102 Mary Andrews 1 0 1 0
    > 103 Clint Backhaus 0 0 0 0
    > 105 Roger Brummont 0 0 0 1
    > 106 Theresa Corbino 0 0 0 0
    > 107 Andrew Cornelius 0 0 0 0
    > 108 Paul Cramer 0 0 0 0
    > 122 John Cronin 6 5 5 7
    > --------------------------------------------------------------------------

    --
    > -
    > Three views and a pivot query vs 4 queries for every row in your CirID
    > table. I suspect this more complicated solution will perform better than

    the
    > subquery approach, but, the only way to be sure is by testing both.
    >
    > HTH,
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
     
    Jeff Uchtman, Apr 12, 2004
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jason Us

    Struts vs. Expresso (2nd try)

    Jason Us, Oct 22, 2003, in forum: Java
    Replies:
    0
    Views:
    296
    Jason Us
    Oct 22, 2003
  2. Replies:
    1
    Views:
    389
  3. Alan Silver
    Replies:
    0
    Views:
    906
    Alan Silver
    Jun 5, 2006
  4. googleboy
    Replies:
    1
    Views:
    947
    Benji York
    Oct 1, 2005
  5. stu7
    Replies:
    15
    Views:
    209
Loading...

Share This Page