Description:
When doing a UNION to get the latest records from two similar tables, I added a unique string value in the select statements for each table so I could distinguish the record type.
The resulting dataset truncates the string value to the len of the shortest string.
How to repeat:
mysql> SELECT
-> e.date_posted,
-> e.entry_id,
-> 'entry' as type
-> FROM
-> entries e,
-> users u
-> WHERE
-> entry_user_id = user_id
-> UNION
-> SELECT
-> c.date_posted,
-> e.entry_id,
-> 'comment' as type
-> FROM
-> comments c,
-> entries e,
-> users u
-> WHERE
-> comment_user_id = user_id AND
-> comment_entry_id = entry_id
-> ORDER BY
-> 1 DESC
-> LIMIT 10;
+---------------------+-------------------------------------+-------+
| date_posted | entry_id | type |
+---------------------+-------------------------------------+-------+
| 2003-09-09 14:24:05 | 87F00A79-8048-D85E-7E794D671DC40D3A | comme |
| 2003-09-09 12:59:37 | 87F00A79-8048-D85E-7E794D671DC40D3A | entry |
| 2003-09-03 06:56:49 | 5423D929-8048-D85E-7E58F312341C0C7B | comme |
| 2003-09-02 20:39:23 | 5423D929-8048-D85E-7E58F312341C0C7B | comme |
| 2003-09-02 20:35:52 | 5423D929-8048-D85E-7E58F312341C0C7B | comme |
| 2003-09-02 20:34:55 | 5423D929-8048-D85E-7E58F312341C0C7B | comme |
| 2003-08-30 17:46:43 | 2B8825F7-8048-D85E-7E41478443F690A3 | comme |
| 2003-08-30 11:41:44 | 203674D3-8048-D85E-7EBB0F5ACFA4C8CC | comme |
| 2003-08-30 11:35:57 | 5423D929-8048-D85E-7E58F312341C0C7B | entry |
| 2003-08-30 11:26:58 | 2B8825F7-8048-D85E-7E41478443F690A3 | comme |
+---------------------+-------------------------------------+-------+
10 rows in set (0.00 sec)
Notice the TYPE column, the values being returned are "comme" and "entry, even though I specified to SELECT the values "comment" and "entry".
Description: When doing a UNION to get the latest records from two similar tables, I added a unique string value in the select statements for each table so I could distinguish the record type. The resulting dataset truncates the string value to the len of the shortest string. How to repeat: mysql> SELECT -> e.date_posted, -> e.entry_id, -> 'entry' as type -> FROM -> entries e, -> users u -> WHERE -> entry_user_id = user_id -> UNION -> SELECT -> c.date_posted, -> e.entry_id, -> 'comment' as type -> FROM -> comments c, -> entries e, -> users u -> WHERE -> comment_user_id = user_id AND -> comment_entry_id = entry_id -> ORDER BY -> 1 DESC -> LIMIT 10; +---------------------+-------------------------------------+-------+ | date_posted | entry_id | type | +---------------------+-------------------------------------+-------+ | 2003-09-09 14:24:05 | 87F00A79-8048-D85E-7E794D671DC40D3A | comme | | 2003-09-09 12:59:37 | 87F00A79-8048-D85E-7E794D671DC40D3A | entry | | 2003-09-03 06:56:49 | 5423D929-8048-D85E-7E58F312341C0C7B | comme | | 2003-09-02 20:39:23 | 5423D929-8048-D85E-7E58F312341C0C7B | comme | | 2003-09-02 20:35:52 | 5423D929-8048-D85E-7E58F312341C0C7B | comme | | 2003-09-02 20:34:55 | 5423D929-8048-D85E-7E58F312341C0C7B | comme | | 2003-08-30 17:46:43 | 2B8825F7-8048-D85E-7E41478443F690A3 | comme | | 2003-08-30 11:41:44 | 203674D3-8048-D85E-7EBB0F5ACFA4C8CC | comme | | 2003-08-30 11:35:57 | 5423D929-8048-D85E-7E58F312341C0C7B | entry | | 2003-08-30 11:26:58 | 2B8825F7-8048-D85E-7E41478443F690A3 | comme | +---------------------+-------------------------------------+-------+ 10 rows in set (0.00 sec) Notice the TYPE column, the values being returned are "comme" and "entry, even though I specified to SELECT the values "comment" and "entry".