MySQL get only rows with a unique value for a certain field

Question

Hi,

I want to get only the rows with a unique value for a certain field (2 actually). My table is like this:

id    senderID    receiverID    ...    ...    ...
_________________________________________________
0     0           1             ...    ...    ...
1     2           1             ...    ...    ...
2     1           0             ...    ...    ...
3     0           2             ...    ...    ...
4     2           0             ...    ...    ...
5     1           2             ...    ...    ...

In this table, id is unique always. senderID is the ID of the user who sent the message, receiverID is the ID of the user who received the message. ... are some fields (like text) that don't really matter and they aren't unique either.

So first of all, I want to get all rows where I (#1) am the sender or the receiver.

SQL: "SELECT id FROM table WHERE senderID='1' OR receiverID='1'";

This will return (0, 1, 2, 5).

But now, I only want all unique records. So the conditions are:

1. 1 should be the senderID or the receiverID.
2. if ((senderID == '1' && "__ receiverID is not yet listed__") || (receiverID == '1' && "__ senderID is not yet listed__")) in pseudo-code.

The final return should be (0, 1).

How do I do this in (My)SQL? I do know how to do this using PHP, but when there're thousands of records it's not fast enough anymore.

Kind regards,

Tim

Answer 1

Something like

SELECT DISTINCT id, senderID, receiverID FROM table WHERE senderID='1' OR receiverID='1';

?

DISTINCT keyword will remove any duplicates from the result. Works good so far, you don't add any other columns other than id, senderID and receiverID.

Otherwise you could also use the GROUP BY clause

SELECT id FROM table WHERE senderID='1' OR receiverID='1' GROUP BY senderID, receiverID;

Answer 2

If you do this, you will get all distinct ID's where you are sender and the distinct id's where you are receiver.

Additionally, the UNION will combine both the results.

In your logic, you can use the 2nd column value ('S'/ 'R') to filter separate the 2 sets if you need to do things separately for sent / received id's.

SELECT distinct id, 'S' as Type FROM table WHERE senderID='1' 
UNION
SELECT distinct id, 'R' as Type FROM table WHERE receiverID='1' 

Answer 3

select min(id) from 
(
  select id, senderID pID from table where receiverID = '1'
  union
  select id, receiverID pID from table where senderID = '1'
) as fred
group by pID;

For your data set, this gives:

+---------+
| min(id) |
+---------+
|       0 |
|       1 |
+---------+