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?