Duplicate records when joining Member, Gift and MembershipTransaction tables

Larry Wheeler:

Malavika Krishnaswamy:

Steven Cinquegrana:

Hi Malavika,


Try restricting your SELECT columns and using DISTINCT.


Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions

 

Hi Steve, 


Thank you for the response. I did try doing that but it still gives me duplicate columns.  


-MK

 

When using distinct are you getting the same number of dupes, or does the distinct reduce the number (per individual)?   We are hosted, so I do not have backend access, but when querying using the RE Query, I do know the gifts can be counted multiple times if they are Split Gifts.   Could that be your issue?

 

 

Hi James, 


Thank you so much for the response. I believe the CONSTIT_GIFTS table was present because if I try joining RECORDS and GIFTS directly using the AccountID and Record.ID, I am not seeing any results at all (Not sure why this is the case). Please see below the revised query that I tried using which did not work. I am not sure what you mean by sub-query. Again I am new to this so thank you for all the inputs. 

 

select distinct m.ID as 'Membership ID', 

        m.Date_Joined as key_date,

mt.ActivityDate as transaction_date,

g.DTE as gift_date,

g.CONSTIT_ID,

        r.FIRST_NAME + ' ' + r.LAST_NAME as 'Primary Member Name', 

        mc.MembershipCategoryID, 

        mt.Dues as 'Dues\\Amount',  

        m.Added_By, 

        u.NAME,

        te.LONGDESCRIPTION as 'Gift Code' 

from MEMBER m 

inner join RECORDS r on r.ID = m.ConstitID 

inner join GIFT g on g.AccountID = r.ID 

inner join TransactionGift tg on tg.GiftID = g.ID 

left join TABLEENTRIES te on te.TABLEENTRIESID = g.GIFT_CODE  

inner join MembershipTransaction mt on mt.ID = tg.TransactionID 

inner join MembershipCategory mc on mc.MembershipCategoryID = mt.Category 

inner join USERS u on u.USER_ID = m.ADDED_BY 

where 

te.LONGDESCRIPTION like '%DMP - Membership%'and 

m.ID = 67978 

and m.Added_By in ('24','49')

order by m.ID,key_date, gift_date

Thanks again, 
MK




James Andrews:

Malavika Krishnaswamy:

 

The query I am using is as seen below:

 

select distinct 

        m.ID as 'Membership ID', 

        m.Date_Joined as key_date,

mt.ActivityDate as transaction_date,

g.DTE as gift_date,

g.CONSTIT_ID,

        r.FIRST_NAME + ' ' + r.LAST_NAME as 'Primary Member Name', 

        mc.MembershipCategoryID, 

        mt.Dues as 'Dues\\Amount',  

        m.Added_By, 

        u.NAME

        --te.LONGDESCRIPTION as 'Gift Code' 

from MEMBER m 

inner join RECORDS r on r.ID = m.ConstitID 

inner join CONSTIT_GIFTS cg on cg.CONSTIT_ID = r.ID 

inner join GIFT g on g.ID = cg.GIFT_ID 

left join TABLEENTRIES te on te.TABLEENTRIESID = g.GIFT_CODE  

inner join MembershipTransaction mt on mt.MembershipID = m.ID 

inner join MembershipCategory mc on mc.MembershipCategoryID = mt.Category 

inner join USERS u on u.USER_ID = m.ADDED_BY 

where te.LONGDESCRIPTION like '%DMP - Membership%' 

--and m.Added_By in ('24','49')

order by m.ID,key_date, gift_date


I am trying to get the Memberships by Gift Date along with the Activity Date from the MembershipTransaction table but when I add the join to the MembershipTransaction table, that is where the duplicates start appearing. For e.g. If there are 4 different Gifts and 4 different transaction dates, I am getting a total of 16 rows for one Member ID. 


Thank you,

MK

 

The query issue ihere s the fact that you're linking to the gifts through constit_gifts and separately linking the membershiptransaction table, so it's going to pull ... I think it's called a cartesian product? of all the gift/transaction combinations. 


What you need to do is link the gift to the transaction via the TransactionGift table, instead of using constit_gifts.  (edited yet again to add: you might need to do a subquery here actually, if you have both pledges and payments linked to membership gifts, so you don't double count them.)