CODEWITHSUNDEEP
mysqlunion
user1643156
user1643156

Reputation: 4537

How to tell which query is the data fetched from in a MySQL statement with UNION

the table

id    string
------------
1     aaa
2     bbb
3     ccc
4     ddd

the query

(SELECT string FROM table WHERE id > 1 ORDER BY id ASC LIMIT 1)    /* num_row = 1 */
UNION
(SELECT string FROM table WHERE id < 1 ORDER BY id DESC LIMIT 1)   /* null */
UNION
(SELECT string FROM table WHERE id > 4 ORDER BY id ASC LIMIT 1)    /* null */
UNION
(SELECT string FROM table WHERE id < 4 ORDER BY id DESC LIMIT 1)   /* num_row = 2 */

The query above will return 2 rows since there are no id=5 and id=0.

How can I tell which queries are these 2 rows fetched from?

that is, num_row = 1 from 1st SELECT, and num_row = 2 from 4th SELECT

Upvotes: 0

Views: 98

Answers (4)

Todd Murray
Todd Murray

Reputation: 423

You could try (inelegant as it is):

(SELECT '1', string FROM table WHERE id > 1 ORDER BY id ASC LIMIT 1) /* num_row = 1 / UNION (SELECT '2', string FROM table WHERE id < 1 ORDER BY id DESC LIMIT 1) / null / UNION (SELECT '3', string FROM table WHERE id > 4 ORDER BY id ASC LIMIT 1) / null / UNION (SELECT '4', string FROM table WHERE id < 4 ORDER BY id DESC LIMIT 1) / num_row = 2

Upvotes: 0

juergen d
juergen d

Reputation: 204766

use a second column to indicate from where the data comes from

(SELECT string, '1st query' as from_where FROM table WHERE ...)
UNION
(SELECT string, '2nd query' as from_whereFROM table WHERE ...)

Upvotes: 0

georgepsarakis
georgepsarakis

Reputation: 1957

You could add an extra constant column with a common alias:

(SELECT string, 'query_1' as query_num FROM table WHERE id > 1 ORDER BY id ASC LIMIT 1)    
UNION
(SELECT string, 'query_2' as query_num FROM table WHERE id < 1 ORDER BY id DESC LIMIT 1)
UNION
(SELECT string, 'query_3' as query_num FROM table WHERE id > 4 ORDER BY id ASC LIMIT 1)    
UNION
(SELECT string, 'query_4' as query_num FROM table WHERE id < 4 ORDER BY id DESC LIMIT 1)

Upvotes: 0

Marco
Marco

Reputation: 57583

You could try

(SELECT 1, string FROM table WHERE id > 1 ORDER BY id ASC LIMIT 1)   
UNION
(SELECT 2, string FROM table WHERE id < 1 ORDER BY id DESC LIMIT 1)  
UNION
(SELECT 3, string FROM table WHERE id > 4 ORDER BY id ASC LIMIT 1)   
UNION
(SELECT 4, string FROM table WHERE id < 4 ORDER BY id DESC LIMIT 1)

Upvotes: 2

Related Questions