StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL complex condition
text
Body
copied!<p>I have following table structure:</p> <p><strong>Users</strong></p> <pre><code>userID (Primary Key), firstName, lastName. Sample data: [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, message-visibile </code></pre> <p>message-visibile can 0 (Public) or 1 (Private)</p> <p>Sample data:</p> <pre><code>[messageID=1, sender_userID=1, receiver_userID=1, messageBody=Hello, message-visibile=1] [2, 1, 2, Second Message, 0] [3, 2, 1, ThirdMessage, 1] [4, 2, receiver_userID=1, FourthMessage, 0] [5, 3, 3, LastMessage, 0] </code></pre> <p>Now, I need to display all messages posted/received by a user ID and his colleagues based on message_visible value (0/1). In above sample data, userID 1 is a colleague of userID 2. So, if I query for userID = 1 with message_visible=1, then I should get message ID 1, 3 only. MessageID = 5 should not appear because 3 is not a colleague of any one.</p> <p>I have tried following query but its not returning expected output:</p> <pre><code>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 = 1 and mes.receiver_userID in (1,2) or mes.receiver_userID = 1 and mes.sender_userID in (1,2) AND mes.message-visibile = 1 </code></pre> <p>Also, in another below condition, its not working</p> <pre><code>where mes.sender_userID in (1,2) or mes.receiver_userID in (1,2) </code></pre> <p>I'll be passing a set of colleagues ID. Am I missing something?</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