Click here to Skip to main content
14,326,246 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have tow table and I want do left join between them , But I want to left join happen only if the count of records that contain the same value of join column in the first table is more or equal the count of records that contain the same value of join column in the second table

what i try :

first i do the count of records per the each group

and then i put condition to filter (a.cnt>=b.cnt)

this is the code :
insert into work.discount_tmp 
select 
	SubsID , MSISDN , EppAcc, User_Name , Bill_Cycle, Tariff , Pack ,Discount_Qual_ID ,Discount_ID , Qualification_Type,Discount_Desc,Sequence,a.GroupID,RuleID,dib_band_id,dib_band_end,dib_charge_ref,DIB_DIS0,dib_disc_type,dib_limit_disc,DIB_MAX_,cd_class_type,ClassID,Class,dgh_group_id ,dgh_inclusion_from,dgh_inclusion_to,20191003 
from
(
	(
		select *, row_number() over (partition by GroupID order by Discount_ID ) as seqnum,
			COUNT(*) over (partition by GroupID order by GroupID ) as cnt 
		from 
			work.disc_band 
		where tbl_dt=20191003 
		order by Discount_ID
	) a
	left join
	(
		select *, row_number() over (partition by GroupID order by cd_class_type,try(cast(coalesce(classid,'0') as bigint)) ) as seqnum,
			count(*) over (partition by GroupID order by GroupID  ) as cnt 
		from 
			work.alltable1 
		where tbl_dt=20191003 
	) b 
	on 
	a.GroupID=b.GroupID  and a.cnt>=b.cnt and a.seqnum=b.seqnum   
 )


But my try not work because the join done first and then the condtion (so the value of cnt in the second table will not stay the same after join done )

any idea how to make that work ?

What I have tried:

insert into work.discount_tmp select SubsID , MSISDN , EppAcc, User_Name , Bill_Cycle, Tariff , Pack ,Discount_Qual_ID ,Discount_ID , Qualification_Type,Discount_Desc,Sequence,a.GroupID,RuleID,dib_band_id,dib_band_end,dib_charge_ref,DIB_DIS0,dib_disc_type,dib_limit_disc,DIB_MAX_,cd_class_type,ClassID,Class,dgh_group_id ,dgh_inclusion_from,dgh_inclusion_to,20191003 from
 ((select *, row_number() over (partition by GroupID order by Discount_ID ) as seqnum,
 COUNT(*) over (partition by GroupID order by GroupID ) as cnt from work.disc_band where tbl_dt=20191003 order by Discount_ID) a
 left join
 (select *, row_number() over (partition by GroupID order by cd_class_type,try(cast(coalesce(classid,'0') as bigint)) ) as seqnum,
 count(*) over (partition by GroupID order by GroupID  ) as cnt 
 from work.alltable1 where tbl_dt=20191003 ) b 
 on a.GroupID=b.GroupID  and a.cnt>=b.cnt and a.seqnum=b.seqnum   )
Posted
Updated 6-Oct-19 21:18pm
v3
Comments
Gerry Schmitz 6-Oct-19 10:51am
   
Count first then. If the counts don't compare, don't join.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Hello
This might help you
;WITH finalGrps AS
(
	SELECT temp.GroupID FROM
	(
		SELECT GroupID, COUNT(*) AS _rows FROM work.disc_band WHERE tbl_dt=20191003
		UNION 
		SELECT GroupID, COUNT(*)*-1 AS _rows FROM work.alltable1 WHERE tbl_dt=20191003 
	) as temp
	GROUP BY temp.GroupID
	HAVING SUM(_rows) >= 0
)
SELECT * FROM finalGrps F
INNER JOIN work.disc_band D ON tbl_dt=20191003 AND F.GroupID = D.GroupID
INNER JOIN work.alltable1 A ON tbl_dt=20191003 AND A.GroupID = D.GroupID


Happy Coding! :)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100