mysql - 连接同一个表的两个查询

Question

I want to join the result of below meantioned queries

SELECT `idCHAT`,`USER_NAME`,`TEXT`,`CURRENT_DATE_TIME`,`CHAT_COUNTERPARTY` FROM chat where USER_NAME='user1' group by `CHAT_COUNTERPARTY` ORDER BY `chat`.`CURRENT_DATE_TIME` DESC 

SELECT `idCHAT`,`USER_NAME`,`TEXT`,`CURRENT_DATE_TIME`,`CHAT_COUNTERPARTY` FROM chat where CHAT_COUNTERPARTY='user1' group by `USER_NAME` ORDER BY `chat`.`CURRENT_DATE_TIME` DESC

Result after UNION of both result is

 idCHAT     USER_NAME   TEXT    CURRENT_DATE_TIME Descending    CHAT_COUNTERPARTY   
 6  user1   helSSSSSSlooooo     2014-10-17 20:45:22     user3
 4  user3   helSSSSSSlooooo     2014-10-17 20:44:00     user1
 3  user2   helSSSSSSlooooo     2014-10-17 20:43:59     user1
 1  user1   helSSSSSSlooooo     2014-10-17 20:40:41     user2

But I want only idCHAT 6&3 from the table because user3 & user2 are CHAT_COUNTERPARTY of user1

Answer 1

select `idCHAT`,`USER_NAME`,`TEXT`,`CURRENT_DATE_TIME`,`CHAT_COUNTERPARTY`, if(USER_NAME='user1',`CHAT_COUNTERPARTY`,`USER_NAME`)as party from(SELECT `idCHAT`,`USER_NAME`,`TEXT`,`CURRENT_DATE_TIME`,`CHAT_COUNTERPARTY` FROM chat where USER_NAME='".$user."' group by `CHAT_COUNTERPARTY` UNION (SELECT `idCHAT`,`USER_NAME`,`TEXT`,`CURRENT_DATE_TIME`,`CHAT_COUNTERPARTY` FROM chat where CHAT_COUNTERPARTY='user1' group by `USER_NAME`) ORDER BY `CURRENT_DATE_TIME` DESC)res group by party order by `CURRENT_DATE_TIME` desc