Skip to content

How to avoid showing duplicate rows because conditions

The problem is where name_1 writes something to name_2 and name_2 writes something to name_1 there is a duplicate and as one result because I SELECT name_1, name_2 with condition

SELECT name_1, name_2 
FROM class_room 
WHERE (name_1='$userInSystem' AND name_2 <>'$userInSystem') 
    OR (name_1<>'$userInSystem' AND name_2 ='$userInSystem' ) 
GROUP BY name_1, name_2 
ORDER BY id DESC

How is it possible to show results with no duplicate rows,

I only want to know if there is communication between user_1 and user_2

Answer

Order the names in a consistent way, then make it DISTINCT.

SELECT DISTINCT LEAST(name_1, name_2) AS name1, GREATEST(name_1, name_2) AS name2
FROM class_room
WHERE (name_1='$userInSystem' AND name_2 <>'$userInSystem') 
    OR (name_1<>'$userInSystem' AND name_2 ='$userInSystem' )