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 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 247180 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..

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: 247180 More actions · Answer 3

Phil Parkin

SSC Guru

Points: 247180

May 28, 2020 at 3:38 pm

#3758210

Have you tried using COUNT(DISTINCT col)?

ScottPletcher SSC Guru Points: 101200 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 5 years, 3 months ago by Lynn Pettis.