MySQL: Merge 3 Query Results by Column

Question

I have 3 different queries that basically sort the same results by different parameters and I would like MySQL to return their results merged into 3 different columns:

SELECT `text` AS `popular` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `hits` DESC LIMIT 10
SELECT `text` AS `recent` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `datetime` DESC LIMIT 10
SELECT `text` AS `matches` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `matches` DESC LIMIT 10

First query returns this:

| popular |
|    A    |
|    B    |
|    C    |

Second query returns this:

| recent |
|   B    |
|   C    |
|   A    |

Third query returns this:

| matches |
|    C    |
|    A    |
|    B    |

I would like to merge those results so that I get this with a single query:

| popular | recent | matches |
|    A    |    B   |    C    |
|    B    |    C   |    A    |
|    C    |    A   |    B    |

This is what I tried so far, but the result I get is totally messed up.

SELECT * FROM
  (SELECT `text` AS `popular` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `hits` DESC LIMIT 10) AS A
  JOIN (SELECT `text` AS `recent` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `datetime` DESC LIMIT 10) AS B ON 1=1
  JOIN (SELECT `text` AS `matches` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `matches` DESC LIMIT 10) AS C ON 1=1

Do you want results that are the same in the 3 queries be in the same row or do you want just unify the 3 queries and get a total of 30 records? Please give an example what your data looks like and what you expect for output. — Frank J
– Frank J, Commented Mar 4, 2016 at 15:47

EduardoCMB · Accepted Answer · 2016-03-07 22:12:52Z

1

Try something like this:

select t1.popular, t2.recent, t3.matches
from       (SELECT @rownum1 := @rownum1 + 1 AS rank, `text` AS `popular` FROM `searches` t, (SELECT @rownum1 := 0) r WHERE `text` LIKE 'Tyr%' ORDER BY `hits`     DESC LIMIT 10) t1
inner join (SELECT @rownum2 := @rownum2 + 1 AS rank, `text` AS `recent`  FROM `searches` t, (SELECT @rownum2 := 0) r WHERE `text` LIKE 'Tyr%' ORDER BY `datetime` DESC LIMIT 10) t2 on t1.rank = t2.rank
inner join (SELECT @rownum3 := @rownum3 + 1 AS rank, `text` AS `matches` FROM `searches` t, (SELECT @rownum3 := 0) r WHERE `text` LIKE 'Tyr%' ORDER BY `matches`  DESC LIMIT 10) t3 on t2.rank = t3.rank

answered Mar 7, 2016 at 22:12

EduardoCMB

5044 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bruce Wayne · Accepted Answer · 2016-03-04 15:55:06Z

0

You can use MySQL 'UNION statement', it can merge more then one select's and return it as one output. So try:

SELECT `text` AS `popular` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `hits` DESC LIMIT 10 UNION SELECT `text` AS `recent` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `datetime` DESC LIMIT 10 UNION SELECT `text` AS `matches` FROM `searches` WHERE `text` LIKE 'Tyr%' ORDER BY `matches` DESC LIMIT 10

answered Mar 4, 2016 at 15:55

Bruce Wayne

192 bronze badges

1 Comment

Tommaso Belluzzo Over a year ago

Without considering the fact that the query returns an error due to ORDER BY statement, this is exactly not what I'm looking for since I want to return the 3 query results organized into 3 columns.

Collectives™ on Stack Overflow

MySQL: Merge 3 Query Results by Column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related