Page 1 of 1

combining 2 resultsets

Posted: Fri Jun 11, 2010 5:02 am
by rupam_jaiswal
HI,
In mysql, I have 2 tables A and B

Table A --- fields

id int(50)
message varchar(10000)
username varchar(100)
type enum('friend','game','admin')
date_posted timestamp

Table B --- fields

id int(11)
message text
username varchar(30)
page_owner varchar(30)
date_posted datetime

with some fields matching and some different.
Now I have to select some values from both tables and combine the result in single something like

SELECT distinct(id), message, username, type, date_posted FROM table A WHERE whereclause
UNION
SELECT distinct(id), message, username, page_owner, date_posted FROM table B WHERE whereclause
ORDER BY date_posted DESC LIMIT 0, 12.

Now my result set shows me

id message username type date_posted
-----------------------------------------------------------------------------------
5 java ammo MrRogue 2010-06-11 13:39:38
4 PHP Honey MrRogue 2010-06-11 12:30:18

24 Rock MrRogue admin 2010-06-08 10:54:52
3 tata MrRogue game 2010-06-03 04:25:24

although 'type' field is present in not present for results of table B and 'page_owner' field is missing in results of table B.

rows with id 5,4 are from table B
rows with id 3,24 are from table A

Its merging the 'type' and 'page_owner' fields into 1 field i,e 'type' in resultset.
I want resultset like this

id message username type page_owner date_posted
------------------------------------------------------------------------------------------------------------
5 java is cool ammo NULL MrRogue 2010-06-11 13:39:38
4 PHP is my page Honey NULL MrRogue 2010-06-11 12:30:18
24 Rock and roll$$$$$ MrRogue admin NULL 2010-06-08 10:54:52
3 tata MrRogue game NULL 2010-06-03 04:25:24

id message username type page_owner date_posted
-----------------------------------------------------------------------------------
5 java ammo MrRogue NULL 2010-06-11 13:39:38
4 PHP Honey MrRogue NULL 2010-06-11 12:30:18

24 Rock MrRogue NULL admin 2010-06-08 10:54:52
3 tata MrRogue NULL game 2010-06-03 04:25:24

is it possible??
Thanks in advance!!!!!!
Regards

Re: combining 2 resultsets

Posted: Fri Jun 11, 2010 3:00 pm
by tr0gd0rr
You can add NULL to the select list:

Code: Select all

SELECT distinct(id) AS id, message, username, NULL AS page_owner, type, date_posted FROM table A WHERE whereclause
UNION
SELECT distinct(id) AS id, message, username, page_owner, NULL AS type, date_posted FROM table B WHERE whereclause
ORDER BY date_posted DESC LIMIT 0, 12