CODEWITHSUNDEEP
mysql
priti narang
priti narang

Reputation: 236

Join two queries of same table

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

Upvotes: 0

Views: 42

Answers (2)

vinod
vinod

Reputation: 2880

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

Upvotes: 1

Nebojsa Susic
Nebojsa Susic

Reputation: 1260

Use union keyword

SELECT `idCHAT`,`USER_NAME`,`TEXT`,`CURRENT_DATE_TIME`,`CHAT_COUNTERPARTY` 
FROM chat 
where USER_NAME='user1' 
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

Upvotes: 0

Related Questions