3

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?

Axl
  • 427
  • 2
  • 7
  • 19
  • Have you tried adding the `DISTINCT` key word to the select `SELECT DISTINCT USERS.user_name, MESSAGES.message`? But if there is a duplicate messages for each user, in this case the `DISTINCT` won't work, and you have to decide which message to select. So in this case which message do you want to display for each user? the latest one? or what? – Mahmoud Gamal Feb 14 '13 at 06:44
  • Also a sample data for the three tables and the desired output will be very helpful. – Mahmoud Gamal Feb 14 '13 at 06:48
  • 1
    Ah, DISTINCT did the trick. I wasn't aware of that key word. I maybe should have worded things differently... there aren't actually duplicate messages in the MESSAGES table, rather I was getting the same message back multiple times in my results, I assume because the same userID could show up multiple times in the GROUP_MATRIX table. – Axl Feb 14 '13 at 06:56

2 Answers2

3

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;
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
2

Since you only want user_name and messages you don't want to use groups and junction table.

Select 
   us.user_name, msg.message 
   from Messages as msg 
   LEFT JOIN USERS as us ON (msg.userID = us.userID)
   groupby msg.messageID
Yashankit Vyas
  • 124
  • 5
  • This works too, I guess I was assuming I needed to query through my junction table to get access to the user_name. – Axl Feb 14 '13 at 07:14