-1

I have two tables: Alerts(22078 rows) and Demographics(1857). The Alerts contain 2 columns: AlertID(Unique) and EndUserID. Each EndUserID can have multiple AlertID. The Demographics table contain 2 columns: EndUserID (Unique) and PIN(Unique and Null).

AlertID EndUserID
101 119
134 119
293 139
89 200
375 119
500 257
EndUserID PIN
119 24001
500 23045
200 25901
1000 Null
439 20018
78 25457

I want to get the PIN for each AlertID in Alerts table from the Demographics. It should be a simple join query in workbench like:

Select a.AlertId, a.EndUserID, b.PIN 
from alerts a join Demographics b  
on a.EndUserID = a.EndUserID;

My output should look like:

AlertID EndUserID PIN
101 119 24001
134 119 24001
293 139 Null
89 200 25901
375 119 24001
500 257 21600

However, I am getting so many number of rows like EndUserID 119 is returning more than 300 records with different PIN even though there are only 59 instances of it in Alerts and just 1 in Demographics.. It is very weird and I am not sure what mistake I am making.

eshirvana
  • 23,227
  • 3
  • 22
  • 38
Pratik Choudhary
  • 27
  • 6
  • Does this answer your question? [What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) – nbk Aug 18 '21 at 17:06
  • @nbk I bet that no. Even if he would change his join type to any of ones you listed the result wouldn't change – Dmitrij Kultasev Aug 18 '21 at 17:08

1 Answers1

1

you are joining all the rows in alerts table with all the rows in Demographics, there is a mistake in alias you are joining the columns on:

Select a.AlertId, a.EndUserID, b.PIN 
from alerts a join Demographics b  
on a.EndUserID = b.EndUserID; -- < here
eshirvana
  • 23,227
  • 3
  • 22
  • 38