combining 2 resultsets

[php_techy]

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

[james438]

Are you saying that you want to sort both tables by a given field where the given field has a different name depending on the table?

If this is the case then take a look at the following:

(SELECT ID, date FROM table1)
Union
(SELECT ID, date2 as date FROM table2)
order by date

In this example date2 in table2 two is given the alias name of "date". Now I can sort both tables at once by the column "date" even though "date" as a name does not exist in table2. To clarify "date2" in table2 has been temporarily renamed to "date" for sorting purposes.

With UNION you can also sort by columns that don't exist in all of the tables provided the column exists in the first table, but that I suspect is another matter than what you are proposing. The results when sorting by a column that does not exist in all tables may be a bit odd; not illogical, but a little odd.

Would you be able to edit your post and put your code and sample results in one of the coding tags or quote tags for readability?

[php_techy]

Thanks a lot!!
found a solution, its

SELECT id, message, username, type, 'NULL' as page_owner,date_posted FROM table A WHERE whereclause
UNION
SELECT id, message, username, 'NULL',page_owner, date_posted FROM table B WHERE whereclause
ORDER BY date_posted DESC LIMIT 0, 12