Retrieving data from SendLog and DataViews simultaneously

Question

I am attempting to reliably retrieve data from both the Data Views and from the Sendlog. The reason is that I need to be able to add two attributes that were added to the SendLog to the DataView data. The below represents the _Click data view and the Sendlog with additional attributes. Essentially, they act as composite keys for each record outside of SFMC

_CLICK CLK       SENDLOG SLG
------          --------
JOBID|SUBID    JOBID|SUBID|OtherAttrbA|OttherAttrbB
12|111            12|111|AAAA|1A2B
12|111            12|222|BBBB|1C2B
13|111            12|333|CCCC|3E4R
14|111            12|444|DDDD|4D2W
14|111            12|555|EEEE|1G4T
14|111            13|111|FFFF|5C2E
12|222            13|222|GGGG|0P5V
12|222            13|333|HHHH|4Z9E
15|333            13|444|IIII|9I1D
                  13|555|JJJJ|7V8E
                  14|111|KKKK|5V3W
                  14|222|LLLL|7V3G
                  14|333|MMMM|2E2E
                  14|444|NNNN|0F8C
                  14|555|OOOO|6Y4G
                  15|111|PPPP|5B8V
                  15|222|QQQQ|0e5G
                  15|333|RRRR|3J8W
                  15|444|SSSS|8X8Z
                  15|555|TTTT|9N8Z



 Desired Result
 CLK.JOBID|CLK.SUBID|SLG.OtherAttribA|SLG.OttherAttrbB
    12|111|AAAA|1A2B
    12|111|AAAA|1A2B
    13|111|FFFF|5C2E
    14|111|KKKK|5V3W
    14|111|KKKK|5V3W
    14|111|KKKK|5V3W
    12|222|BBBB|1C2B
    12|222|BBBB|1C2B
    15|333|RRRR|3J8W

The issue that I'm running in to, I think, is in the Joining of these two tables. As you might expect, I'm getting duplicates somewhere. For example, if I run:

SELECT SubscriberID
FROM _Click

I will get 519 records (as of now)

However, when I run:

SELECT
  a.JobID,
  a.SubscriberID,
  b.OtherAttribA,
  b.OtherAttribB
FROM _Click a
LEFT JOIN SendLog b on a.SubscriberID = b.SubID AND a.JobID = b.JobID

I get 552 records. I had considered using GROUP BY, but that feels like it would be covering up an issue, right?

Answer 1

As per comment:

The SendLog is not necessarily in sync with the _DataViews. It depends on creation time, data retention settings and more.

If a LEFT JOIN on SendLog retrieves more records than a COUNT(*) on _Click, this means you have multiple records in SendLog for the same SubID and JobID. You can either try to join it on BatchID or have a look at the SendLog first to see how the data looks like.

With only 519 clicks this should be fairly easy and straight forward.