Tweet

Recently I have experienced a situation while working on a PHP project with MySQL, where I had to combine 2 SQL result set while grouping with common key for the entire combines result.

I found a thread on mysql forums ( http://lists.mysql.com/mysql/182163 ) that solved this problem.

Let’s make a messaging example:

Table: Messages
==============
MsgID | Nick | Sender | Receiver

Here we have to display all the messages which either sent to me or sent by me. Let’s make a SQL with UNION and GROUP

SELECT id,nickname,messenger FROM (
SELECT msgid as id,name as nickname,sender as senderid as senderid,receiver as messenger FROM Messages WHERE SenderId = '1' UNION SELECT msgid as id,name as nickname,sender as messenger,receiver as messenger FROM Messages WHERE ReceiverId = '1' ) as temptable GROUP BY messenger

As you noticed, we have made a temporary table here named “temptable”. Make sure both quries contains same number of columns and the same alias for the column names.

In the results, the messenger would be the one, to which user with ’1′ is communicating weather for outgoing messages or incoming messages.

Hope it will help you somewhere.

Tags: database, dba, example, group by, mysql, programming, query, sql, tech, union