Re: 2nd try, left join

From: Bob Barrows (reb01501_at_NOyahoo.SPAMcom)
Date: 04/09/04 

Date: Fri, 9 Apr 2004 13:12:36 -0400

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"

Relevant Pages

  • Re: 2nd try, left join
    ... Thank Bob. ... 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. ... >> You're going to need to provide some sample data and desired results ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Get Value from Next Record for this record
    ... -some sample data ... "Bob" wrote in message ... >>This might be doable in a query. ... >>MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: UDF and SQL2000 - Why doesnt this work?
    ... Basically This is what I want to do - I have created a query ... as DDL (i.e. CREATE TABLE statements; ... Sample data that illustrates the problem, ... Expected output from the sample data supplied; ...
    (microsoft.public.sqlserver.programming)
  • Re: New to SQL and trying multiple table joins
    ... > Can't be certain of how to write this query for you without DDL (CREATE ... Previous msg edited plus added in keys, and sample data at end. ... EmpAvailID (Primary Key), EmpID, EAMonthID(Foreign ... AbilityID, EmpID, MainAbility ...
    (microsoft.public.sqlserver.programming)
  • Re: find missing time periods
    ... User and UserShiftStart in sample data, ... I believe your unmatched query actually ... FROM [Time Periods], Splits; ...
    (microsoft.public.access.queries)