Trying do find not exists users

1

Hi.

    select *
  from  [dbo].[cognetic_data_transaction] VL 
  inner join [dbo].[cognetic_members_membership] VLM on VL.transaction_membershipid = VLM.membership_id
  inner join [dbo].[cognetic_members_club] VLC on VLC.club_id = VLM.membership_clubid
  inner join [dbo].[cognetic_core_person] VLCP on VLCP.person_id = VLM.membership_personid
  inner join cognetic_members_card CMC on CMC.card_membershipid = VLM.membership_id
   where  VLC.club_id = 5
  and CMC.card_status = 10

The problem here is that a card status can also be other than 10 so I need to find only people that has only a card with status 10 but I guess I am doing something wrong and it does not work (find users that have card 10 and not 5)

  select *
  from  [dbo].[cognetic_data_transaction] VL 
  inner join [dbo].[cognetic_members_membership] VLM on VL.transaction_membershipid = VLM.membership_id
  inner join [dbo].[cognetic_members_club] VLC on VLC.club_id = VLM.membership_clubid
  inner join [dbo].[cognetic_core_person] VLCP on VLCP.person_id = VLM.membership_personid
  inner join cognetic_members_card CMC on CMC.card_membershipid = VLM.membership_id
  -- 2 = WebSite User Creation , NULL = Loyalty , 15 = CallCenter
 --where person_lastname like '%PETCH%' and person_firstName like 'ALEXANDRA%' 
 --and  transaction_membershipid like '%BDS1%'
   where  VLC.club_id = 5
  and CMC.card_status = 10 
  
  and not exists ( select *
  from  [dbo].[cognetic_data_transaction] VL 
  inner join [dbo].[cognetic_members_membership] VLM on VL.transaction_membershipid = VLM.membership_id
  inner join [dbo].[cognetic_members_club] VLC on VLC.club_id = VLM.membership_clubid
  inner join [dbo].[cognetic_core_person] VLCP on VLCP.person_id = VLM.membership_personid
  inner join cognetic_members_card CMC on CMC.card_membershipid = VLM.membership_id
    where  VLC.club_id = 5
  and CMC.card_status = 5)

This will not work

2

Hmmm.
I don't have a standard column that I can check so I must look for firstname and lastname to see rows of a person(because a membership_personid might have more than one rows).
Is this viable?

  select * 
  from  [dbo].[cognetic_data_transaction] VL 
  inner join [dbo].[cognetic_members_membership] VLM on VL.transaction_membershipid = VLM.membership_id
  inner join [dbo].[cognetic_members_club] VLC on VLC.club_id = VLM.membership_clubid
  inner join [dbo].[cognetic_core_person] VLCP on VLCP.person_id = VLM.membership_personid
  inner join cognetic_members_card CMC on CMC.card_membershipid = VLM.membership_id
  
   where  VLC.club_id = 5
 -- and CMC.card_status = 10 
  and not exists ( select *
  from  [dbo].[cognetic_data_transaction] VL2 
  inner join [dbo].[cognetic_members_membership] VLM2 on VL2.transaction_membershipid = VLM.membership_id
  inner join [dbo].[cognetic_members_club] VLC2 on VLC2.club_id = VLM.membership_clubid
  inner join [dbo].[cognetic_core_person] VLCP2 on VLCP2.person_id = VLM.membership_personid
  inner join cognetic_members_card CMC2 on CMC2.card_membershipid = VLM.membership_id
    where VLC2.club_id = 5 and  VLCP2.person_lastname = VLCP.person_lastname
	and VLCP2.person_firstName = VLCP.person_firstName
	 and  CMC2.card_status in(1,2,3,4,5,6,7,8,9 ))
3

Guessing, but this perhaps

inner join cognetic_members_card CMC on CMC.card_membershipid = VLM.membership_id
   where  ...
  and CMC.card_status = 10

change to:

inner join cognetic_members_card CMC 
     on CMC.card_membershipid = VLM.membership_id
    and CMC.card_status = 10
LEFT OUTER JOIN cognetic_members_card AS CMC2 
     on CMC2.card_membershipid = VLM.membership_id
    and CMC2.card_status <> 10
WHERE ...
    AND CMC2.card_membershipid IS NULL -- No Status<>10 row exists

That should find members that ONLY have a Status=10 row in [cognetic_members_card]

1 Like
4

Hi.
This seems to work, however I'm surprised by the result as it seems that most of the members in group 7 only have a card status 10 but 2/3 of the members in group 5 have other card status.
I'll have to look this up with HR as this is surprising, if the query is correct.

Let me give some more info, in order to very the query.
A member can have more than 1 member card cognetic_members_card, the VLCP.person_id however is unique so selecting a member with more than 1 card in will result in multiple result of the same person_id.

Also I should probably remove the cognetic_data_transaction as this was for another search (member transactions in general) and I do not think I need this.

thanks.

5

Hmm.
Yes it work at some point.
I get transactions with card = 5 when a person has been issued another card on cognetic_members_card.Card_Cardnumber and thus having 2 cards and above