combining 2 resultsets

[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

[a.stilliard]

Have you tried using AS?

e.g.

SELECT distinct(id), message, username, type AS newType, date_posted FROM table A WHERE whereclause

UNION

SELECT distinct(id), message, username, page_owner AS newType, date_posted FROM table B WHERE whereclause

ORDER BY date_posted DESC LIMIT 0, 12.