Link to home
Create AccountLog in
Avatar of bender007
bender007

asked on

4 table join and count

I do a 4 table join, but i need to return the count from 2 tables. How do i do that ?

I am needing individual counts from table C and the entire count of the join result.
SELECT AccountNumber,TransactionCode,Count(*) as FileCount,CompanyName,CompanyIdentification,TransactionDate FROM A WITH (NOLOCK)
    INNER JOIN B WITH (NOLOCK)
        ON (A.TransactionNumber = B.TransactionNumber)
    INNER JOIN C WITH (NOLOCK)  
        ON (C.BatchID = B.BatchID)
    INNER JOIN D WITH (NOLOCK)
        ON (D.EntryID = C.EntryID)
WHERE (LEFT(D.AddendaInformation,3) = 'MD1') AND (C.TransactionCode IN ('1','6','3') AND (A.TransactionDate  BETWEEN @begin_date AND @end_date) AND (A.AccountNumber = @Account_Number))
GROUP BY CompanyName,CompanyIdentification,TransactionDate,TransactionCode,AccountNumber ORDER BY TransactionDate

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of bender007
bender007

ASKER

Thanks
no you need to explain explicitly what you want to count in regard to c...

please explain the relationships between the tables (which are 1:1 or 1:m,...)

and what your trying to count...
   the number of joined c rows ... made distinct by what key?
   something else?

can you give some sample data?
also from which tables do the select columns come from ...
Its a 1:m relationship. The C tables are Child relationship to parent B. I need the total number of rows being returned from the query.

A columns:
userid (adam, betty, gil, jack, larry, steve, tyler)
full_name (firstname lastname)
TransactionNumber(uid)Auto gen

B has columns:
batch_id (auto number created when new full_name is added)
TransactionNumber(uid)


C  has columns:
entry_id (auto number created when new entry is added)
batch_id(int)

so  

count(*)   will give you total rows  A* B * C * D

and Count(distinct C.entry_id)  will give you the total distinct C rows within the A*B*C*D

but are you after B*C ?  
Yes I need B*C.
Should I post this as a new question ?The result I got was limited to the rows returned by the total rows, A * B * C * D