Jump to content

combining 2 resultsets


rupam_jaiswal

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.