SQLite - Join returning duplicate result

user_42428 0
Viewed 33

I've read through dozens of posts on this so apologies if it's easily answered but I'm just not getting it.

Table Name: users

user_id     group_id     house_name
==========================================
923828395   1            Alpha
722161580   2            Beta
923828395   1            Gamma

Users can be in multiple Groups, and in a different House per Group.

Table Name: points

user_id     group_id     points   term_id
=====================================================================================
722161580   1            18       02078e51
923828395   1            11       02078e51
923828395   2            140      81450fc1

Users can accumulate points in each Group they reside in. In the data above, user_id 923828395 exists in both group_id 1 and 2, accumulating points in both.

Query:

SELECT users.user_id, users.house_name, points.points, points.group_id, points.term_id
FROM users
JOIN points ON points.user_id = users.user_id
WHERE points.term_id = "02078e51" AND points.group_id = "1"

I'm trying to get this to just return 2 rows. However it's returning:

users.user_id     users.house_name points.points points.group_id points.term_id
722161580         Beta             18            1               02078e51
923828395         Alpha            11            1               02078e51
923828395         Gamma            11            1               02078e51

I think this is because I've got something wrong with my WHERE .. I've tried flipping that to just an AND statement but I get the same results.

join sqlite
1 Answers

You should join the tables on group_id also:

SELECT u.user_id, u.house_name, p.points, p.group_id, p.term_id
FROM users u JOIN points p
ON p.user_id = u.user_id AND p.group_id = u.group_id
WHERE p.term_id = '02078e51' AND p.group_id = '1'

See the demo.

user_104988280
Related