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: 246695 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..

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

Have you tried using COUNT(DISTINCT col)?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ScottPletcher SSC Guru Points: 100947 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) A socialist is someone who will give you the shirt off *someone else's* back.

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 3 years, 11 months ago by Lynn Pettis.