Just want record counts from two tables with 1:Many relationship

Question

Just want record counts from two tables with 1:Many relationship

SSCrazy

Points: 2257

May 28, 2020 at 3:22 pm

select 
count (H.Customs_Entry_Num)
,count(L.Customs_Entry_Num)
FROM ADHOC.ATS_ESH H  -- this table has only unique Entry Numbers
INNER JOIN adhoc.ATS_ESL L  -- this  table is the child table (line level)  
-- ESH has a 1:many relationship with ESL
 ON h.TRANS_SK = l.TRANS_SK
WHERE
H.Importer = 'XXXX'
and H.Entry_Summary_Date > '4/1/2020'

I want the total records from ESH and the total number of records from ESL.

There are generally 5 rows in ESL per unique entry number in ESH.

thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Your query will never get total record counts for the tables because you are filtering out rows in the WHERE clause before the COUNT is applied..

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jeffshelix SSCrazy Points: 2257 More actions · Answer 2

the tables contain more than 1 Importer and records go back years. I only want the row counts for the filtered results.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Have you tried using COUNT(DISTINCT col)?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101121 More actions · Answer 4

I'm not 100% sure of what you need, but I think the total from the first table is being overstated. If so, then maybe this:

select 
count (H.Customs_Entry_Num)
,max(L.[Child_Customs_Entry_Num_Count]) as [Child_Customs_Entry_Num_Count]
FROM ADHOC.ATS_ESH H
CROSS APPLY (
    SELECT COUNT(Customs_Entry_Num) AS [Child_Customs_Entry_Num_Count]
    FROM adhoc.ATS_ESL L
    WHERE L.TRANS_SK = H.TRANS_SK
) AS L 
WHERE
H.Importer = 'XXXX'
and H.Entry_Summary_Date > '20200401'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

edit: never mind, don't think it would work after all.

This reply was modified 4 years, 6 months ago by Lynn Pettis.