CODEWITHSUNDEEP
mysqlsql
user1
user1

Reputation: 262

SQL Joins issues and where clause

I have the following messages table :

enter image description here

What I doing is a chat history, I trying to show all the messages between the two users I have tried different queries but end up only getting the 3 messages that where received by 'E124' but I cant show all 6 messages including the ones that where received by '1'

First attempt

SELECT
  m.id,
  m.reciever,
  m.sender,
  m.message,
  m.TIME,
  m.reciver_read,
  r.userID,
  r.firstname AS senderName,
  r.lastname AS senderSurname,
  d.donorCode,
  d.firstname AS donorFname,
  d.lastname AS donorLname 
FROM
  messages m 
  INNER JOIN
    recipients r 
    ON r.userID = m.sender 
  INNER JOIN
    donors d 
    ON d.donorCode = m.reciever 
    AND m.reciever = 'E124' 
    AND r.userID = 1 
ORDER BY
  m.TIME ASC

produces :

enter image description here

second try

Select   m.id,
         m.reciever,
         m.sender,
         m.message,
         m.time,
         m.reciver_read,
         r.userID,
         r.firstname As senderName,
         r.lastname As senderSurname,
         d.donorCode,
         d.firstname As donorFname,
         d.lastname As donorLname
From     messages m
Inner Join recipients r
    On r.userID = m.sender
Inner Join donors d
    On d.donorCode = m.reciever
Join     recipients
    On m.reciever = recipients.userID
Where    m.reciever = 'E124'
         Or m.reciever = 1
            And r.userID = 1
Order By m.time Asc;

displays no results at all

I would like help on how can I also get the messages that where received by '1' in a single result?

Upvotes: 2

Views: 76

Answers (6)

Kavin
Kavin

Reputation: 303

Try this.. hope it helps you..

SELECT
  m.id,
  m.reciever,
  m.sender,
  m.message,
  m.TIME,
  m.reciver_read,
  r.userID,
  r.firstname AS senderName,
  r.lastname AS senderSurname,
  d.donorCode,
  d.firstname AS donorFname,
  d.lastname AS donorLname 
FROM
  messages m 
  INNER JOIN recipients r ON r.userID IN(m.sender,m.reciever) 

  INNER JOIN donors d ON d.donorCode IN(m.sender,m.reciever) 

WHERE m.reciever IN('E124','1') AND r.userID = 1 

ORDER BY
  m.TIME ASC

Upvotes: 3

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

You should try following query. 

SELECT
  m.id,
  m.reciever,
  m.sender,
  m.message,
  m.TIME,
  m.reciver_read,
  r.userID,
  r.firstname AS senderName,
  r.lastname AS senderSurname,
  d.donorCode,
  d.firstname AS donorFname,
  d.lastname AS donorLname 
FROM
  messages m 
  INNER JOIN recipients r ON r.userID = m.sender 
  INNER JOIN donors d ON d.donorCode = m.reciever 
    AND m.reciever in ('E124')
    AND m.sender in ('E124')
    AND r.userID = 1

Union All


SELECT
  m.id,
  m.reciever,
  m.sender,
  m.message,
  m.TIME,
  m.reciver_read,
  r.userID,
  r.firstname AS senderName,
  r.lastname AS senderSurname,
  d.donorCode,
  d.firstname AS donorFname,
  d.lastname AS donorLname 
FROM
  messages m 
  INNER JOIN recipients r ON r.userID = m.sender 
  INNER JOIN donors d ON d.donorCode = m.reciever 
    AND m.reciever in ('1')
    AND m.sender in ('1')
    AND r.userID = 1

Upvotes: 1

pradeepmanker
pradeepmanker

Reputation: 11

You can use IN Clause for this.

SELECT
 m.id,m.reciever,m.sender,m.message,m.TIME,
 m.reciver_read,r.userID,r.firstname AS senderName,
 r.lastname AS senderSurname, d.donorCode,
 d.firstname AS donorFname, d.lastname AS donorLname 
FROM messages m 
INNER JOIN recipients r ON r.userID = m.sender 
INNER JOIN donors d ON d.donorCode = m.reciever 
AND m.reciever IN('E124','1') 
AND r.userID = 1
ORDER BY m.TIME ASC

Upvotes: 0

Vadym Pechenoha
Vadym Pechenoha

Reputation: 574

Try this:

SELECT
  m.id,
  m.reciever,
  m.sender,
  m.message,
  m.TIME,
  m.reciver_read,
  r.userID,
  r.firstname AS senderName,
  r.lastname AS senderSurname,
  d.donorCode,
  d.firstname AS donorFname,
  d.lastname AS donorLname 
FROM
  messages m 
INNER JOIN
  recipients r ON r.userID = m.sender 
INNER JOIN
  donors d ON d.donorCode = m.reciever 
WHERE
  m.sender = '1' AND m.reciever = 'E124' OR
  m.sender = 'E124' AND m.reciever = '1'
ORDER BY
  m.TIME ASC

Upvotes: 1

Kim Lage
Kim Lage

Reputation: 117

Try using lime this:

(m.reciever = 'E124'
     Or m.reciever = 1) 
        And r.userID = 1

Upvotes: 0

RealCheeseLord
RealCheeseLord

Reputation: 795

Maybe: 

FROM messages m 
JOIN recipients r 
  ON r.userID = m.sender 
 AND (m.reciever = 'E124' OR m.reciever = '1')
 AND (m.sender = 'E124' OR m.sender = '1')
LEFT JOIN donors d 
  ON d.donorCode = m.reciever 
 AND d.donorCode = 'E124'

Upvotes: 1

Related Questions