Databases
StackOverflow2013
Postsdbo
POSQL Join two table with complex condition
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Join two table with complex condition
    text
    copied!<p>I am trying to merge data from two tables. Following is the structure:</p> <p><strong>Users</strong></p> <pre><code>userID (Primary Key), firstName, lastName. </code></pre> <p>Sample data:</p> <pre><code>[1, John, Smith] [2, Steve, Lan] [3, Matt, Smith] </code></pre> <p><strong>Message</strong></p> <pre><code>messageID (Primary Key), sender_userID, receiver_userID, messageBody. </code></pre> <p>Sample data: </p> <pre><code>[1, 1, 1, Hello] [2, 1, 2, Second Message] [3, 2, 1, ThirdMessage] [4, 2, 1, FourthMessage] [5, 3, 3, LastMessage] </code></pre> <p>Now, I need to display all messages posted/received by a user ID and his colleagues.</p> <p>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.</p> <p><strong>Result with unique messageID should be:</strong> </p> <pre><code>Message.messageID, Users.userID (Sender), Users.firstName (Sender), Users.lastName (Sender), Users.userID (Receiver), Users.firstName (Receiver), Users.lastName (Receiver), Message.messageBody </code></pre> <p>I know the query can done using JOIN, but I am new to SQL and some how not able to get the desired output.</p> <p>Any help would be appreciated.</p> <p>Thanks</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload