CODEWITHSUNDEEP
mysqlselectlimitinner-joinunion
cwiggo
cwiggo

Reputation: 2609

Is there any way to get 2 rows from each of the queries?

I have the following MYSQL query:

SELECT m.id AS id, 'admin' AS type, m.title AS title, m.message AS message, m.link AS link, m.image AS image, 'White' AS colour_scheme
                FROM x_elder_messages m
                UNION
                SELECT a.id AS id, 'evidence' AS type, a.name AS title, a.about AS message, CONCAT('http://www.aaa.com/x/view/ambition/',a.id,'/') AS link, CONCAT('http://www.aaa.com/x/x_images/x_ambitions/current/thumb/',ai.image_id,'.jpg') AS image, 'White' AS colour_scheme
                FROM x_ambitions a
                INNER JOIN x_ambition_images ai
                ON a.id = ai.ambition_id
                UNION 
                SELECT u.id AS id, 'profile_update' AS type, CONCAT(u.x_first_name,' ',u.x_last_name) AS title, CONCAT(CONCAT(u.x_first_name,' ',u.x_last_name),' has recently updated their profile') AS message, CONCAT('http://www.aaa.com/x/view/',u.id,'/') AS link, CONCAT('http://www.aaa.com/x/x_images/x_users/current/thumb/',i.image_id,'.jpg') AS image, 'Dark' AS colour_scheme
                FROM x_user u
                INNER JOIN x_user_images i
                ON u.id = i.user_id
                ORDER BY RAND()

I have the output to have the same column names, so that I can deal with the result as a whole. But is there any way to LIMIT 2 for each of the queries, so that it would grab 2 admin, 2 ambition, 2 users?

Thanks

Upvotes: 1

Views: 50

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Yes, you can put a limit in each subquery:

(SELECT m.id AS id, 'admin' AS type, m.title AS title, m.message AS message, m.link AS link, m.image AS image, 'White' AS colour_scheme
                FROM x_elder_messages m
 LIMIT 2
) UNION
(SELECT a.id AS id, 'evidence' AS type, a.name AS title, a.about AS message, CONCAT('http://www.aaa.com/x/view/ambition/',a.id,'/') AS link, CONCAT('http://www.aaa.com/x/x_images/x_ambitions/current/thumb/',ai.image_id,'.jpg') AS image, 'White' AS colour_scheme
                FROM x_ambitions a
                INNER JOIN x_ambition_images ai
                ON a.id = ai.ambition_id
 LIMIT 2
) UNION 
(SELECT u.id AS id, 'profile_update' AS type, CONCAT(u.x_first_name,' ',u.x_last_name) AS title, CONCAT(CONCAT(u.x_first_name,' ',u.x_last_name),' has recently updated their profile') AS message, CONCAT('http://www.aaa.com/x/view/',u.id,'/') AS link, CONCAT('http://www.aaa.com/x/x_images/x_users/current/thumb/',i.image_id,'.jpg') AS image, 'Dark' AS colour_scheme
                FROM x_user u
                INNER JOIN x_user_images i
                ON u.id = i.user_id
 LIMIT 2
)
ORDER BY RAND() ;

In addition, you should use union all unless you want to intentionally remove duplicates (that adds extra processing). And when using limit you typically want an order by to specify which rows you get.

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT a.id, a.type, a.title, a.message, a.link, a.image, a.colour_scheme 
FROM (SELECT m.id AS id, 'admin' AS TYPE, m.title AS title, m.message AS message, m.link AS link, m.image AS image, 'White' AS colour_scheme
      FROM x_elder_messages m
      ORDER BY RAND() LIMIT 2
    ) AS a
UNION
SELECT b.id, b.type, b.title, b.message, b.link, b.image, b.colour_scheme 
FROM (SELECT a.id AS id, 'evidence' AS TYPE, a.name AS title, a.about AS message, CONCAT('http://www.aaa.com/x/view/ambition/',a.id,'/') AS link, CONCAT('http://www.aaa.com/x/x_images/x_ambitions/current/thumb/',ai.image_id,'.jpg') AS image, 'White' AS colour_scheme
      FROM x_ambitions a
      INNER JOIN x_ambition_images ai ON a.id = ai.ambition_id
      ORDER BY RAND() LIMIT 2
    ) AS b
UNION 
SELECT c.id, c.type, c.title, c.message, c.link, c.image, c.colour_scheme 
FROM (SELECT u.id AS id, 'profile_update' AS TYPE, CONCAT(u.x_first_name,' ',u.x_last_name) AS title, CONCAT(CONCAT(u.x_first_name,' ',u.x_last_name),' has recently updated their profile') AS message, CONCAT('http://www.aaa.com/x/view/',u.id,'/') AS link, CONCAT('http://www.aaa.com/x/x_images/x_users/current/thumb/',i.image_id,'.jpg') AS image, 'Dark' AS colour_scheme
      FROM x_user u
      INNER JOIN x_user_images i ON u.id = i.user_id
      ORDER BY RAND() LIMIT 2
    ) AS c

Upvotes: 1

Sigur
Sigur

Reputation: 317

I'm not familiar with MySQL SQL, but I would suggest restricting each of the subqueries to two rows. On Ms-sqlserver one would use 'top 2' for each select. To be able to differentiate between to subsets add a static value for each. On the first select you could use in the select statement 'type = elder_message'.

Upvotes: 0

Related Questions