SQL Join two table with complex condition

Question

I am trying to merge data from two tables. Following is the structure:

Users

userID (Primary Key),
firstName,
lastName.

Sample data:

[1, John, Smith]
[2, Steve, Lan]
[3, Matt, Smith]

Message

messageID (Primary Key),
sender_userID,
receiver_userID,
messageBody.

Sample data:

[1, 1, 1, Hello]
[2, 1, 2, Second Message]
[3, 2, 1, ThirdMessage]
[4, 2, 1, FourthMessage]
[5, 3, 3, LastMessage]

Now, I need to display all messages posted/received by a user ID and his colleagues.

In above sample data, userID 1 is a colleague of userID 2. So, if I query for userID = 1, then I should get message ID 1, 2, 3 [Since Message.sender_userID IN (1,2)] and 4 [Since Message.receiver_userID= 1]. MessageID = 5 should not appear because 3 is not a colleague of any one. Along with this, it should print firstName and lastName for both sender_userID, receiver_userID.

Result with unique messageID should be:

Message.messageID, 
Users.userID (Sender), 
Users.firstName (Sender), 
Users.lastName (Sender), 
Users.userID (Receiver), 
Users.firstName (Receiver), 
Users.lastName (Receiver), 
Message.messageBody

I know the query can done using JOIN, but I am new to SQL and some how not able to get the desired output.

Any help would be appreciated.

Thanks

I will be passing a set of userID who are colleagues of a userID. — johnk1, Jun 18 '11 at 19:25
Possible duplicate: http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join — , Jun 18 '11 at 19:31

score 0 · Answer 1 · answered Jun 18 '11 at 19:24

Assuming you mean "where sender or receiver = 1" to define "colleagues" based on your logic. If colleague is defined elsewhere we'd need more info.

SELECT 
   M.messageID,
   USend.firstName AS firstNameFrom,
   USend.lastName AS lastNameFrom,
   URrec.userID,
   URrec.firstName AS firstNameTo,
   URrec.lastName AS lastNameTo,
   M.messageBody
FROM
   Message M
   JOIN
   User URrec ON M.receiver_userID = URrec.userID
   JOIN
   User USend ON M.sender_userID = USend.userID
WHERE
   M.receiver_userID = 1 OR M.sender_userID = 1

Alex Aza · Accepted Answer · 2011-06-18T20:03:00.650

0

I assume you define colleagues with a list. Below (1, 2) - is a list of colleagues.

select 
    mes.MessageId, 
    usr1.sender_userID SenderUserId, 
    usr1.firstName SenderFirstName, 
    usr1.lastName SenderLastName, 
    usr2.userID ReceiverUserId, 
    usr2.firstName ReceiverFirstName, 
    usr2.lastName ReceiverLastName, 
    mes.messageBody
from Message mes
    join Users usr1 on
        mes.sender_userID = usr1.userId
    join Users usr2 on
        mes.receiver_userID = usr2.userId
where 
    mes.sender_userID in (1,2) or 
    mes.receiver_userID in (1,2)

[EDIT] If you need to show only those messages that were sent or received by user 1 to/from his colleagues, then condition would be:

where 
    mes.sender_userID = 1 and mes.receiver_userID in (1,2) or 
    mes.receiver_userID = 1 and mes.sender_userID in (1,2)

1 - is user of interest
(1, 2) - is the colleagues list. (User 1 is a colleague to himself :))

edited Jun 18 '11 at 20:03

answered Jun 18 '11 at 19:29

Alex Aza

76,499
26
155
134

Thanks Alex. I tried with mes.receiver_userID = 1 but it returns records only when receiver_userID is an colleague. I would also like to get the records where sender_ID = 1 and receiver_ID is an colleague or not. – johnk1 Jun 18 '11 at 19:52
@johnk1 - not sure I understand. This one condition `mes.sender_userID in (1,2) or mes.receiver_userID in (1,2)` will show both whenever 1 is sender or receiver. If this is not what you want, you might need to refine your question with a better example. – Alex Aza Jun 18 '11 at 19:58
Let me take my example again: [messageID=1, sender_userID=1, receiver_userID=1, messageBody=Hello] [2, 1, 2, Second Message] [3, 2, 1, ThirdMessage] [4, 2, receiver_userID=1, FourthMessage] [5, 3, 3, LastMessage] [6, 1, 7, LastMessage] [7, 2, 9, LastMessage] [8, 1, 9, LastMessage] So, in above case, for userID = 1 and his colleague set as (1,2), the query should return me messageID 1,2,3,4,6,7,8. Note that in messageID 7,8, receiverID = 9 which is not in colleague set. receiverID will always be equal to 1. I hope this doesn't confuse more. – johnk1 Jun 18 '11 at 20:06
@johnk1 - I'm sorry. I don't understand. My first query will return 1,2,3,4,6,7,8 for this example, won't it? – Alex Aza Jun 18 '11 at 20:12
@Alex You are right. The first query does the magic. It was problem with my data. There were no records in user table for userID=3,7,9. So no messageID record with those userID was getting pulled. Thanks. Don't know when I would start writing quick and instant queries like you :) . – johnk1 Jun 18 '11 at 20:26
@Alex One more thing: I have added a new field which will indicate whether the message is private or public. Private = 1 and public = 0. Modified your suggested query: where mes.sender_userID = 1 and mes.receiver_userID in (1,2) or mes.receiver_userID = 1 and mes.sender_userID in (1,2) AND mes.message_visibility = 1 I created only one private record and executed above query. Instead of the getting only one record, I am getting more than one. Geting wrong records in case of message_visibility = 0 too. How do I fix it? Thanks. – johnk1 Jun 19 '11 at 02:10
I don't understand, although trying hard. I suggest creating a new question showing sample input and expected output. I or somebody else will help you out. – Alex Aza Jun 19 '11 at 02:16

SQL Join two table with complex condition

2 Answers2