I have a fairly simple database consisting of 4 tables:
Table 1: USERS
Columns: userID, user_name
Table 2: GROUPS
Columns: groupID, group_name
Table 3 (Junction Table): GROUP_MATRIX
Columns: userID, groupID
Table 4: Messages
Columns: messageID, message, userID, groupID
I'd like to do a query for all messages with results in the following format:
user_name, message
I formed the query like so:
SELECT USERS.user_name, MESSAGES.message
FROM GROUP_MATRIX
JOIN USERS on GROUP_MATRIX.userID = USERS.userID
JOIN MESSAGES on GROUP_MATRIX.userID = MESSAGES.userID
It sort of works, but I'm getting some duplicate results back. It seems that if a user_ID shows up in the GROUP_MATRIX more than once, that is when I get duplicate results for that user. Clearly I don't understand the JOIN's I'm trying to do, can someone help me understand what I'm doing wrong?
Use the keyword DISTINCT
in the SELECT
clause like this:
SELECT DISTINCT USERS.user_name, MESSAGES.message
FROM GROUP_MATRIX
JOIN USERS on GROUP_MATRIX.userID = USERS.userID
JOIN MESSAGES on GROUP_MATRIX.userID = MESSAGES.userID;