Content returned one column - multiple tables

Hi

Would you please recommend the best way to get content from two tables in on column. Say I have the following tables:

CREATE TABLE  `db`.`large_content` (
  `id` int(11) NOT NULL auto_increment,
  `member_id` int(11) NOT NULL,
  `page_id` tinyint(4) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

and also have table

CREATE TABLE  `db`.`content` (
  `id` int(11) NOT NULL auto_increment,
  `member_id` int(11) NOT NULL,
  `page_id` tinyint(4) NOT NULL,
  `content` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

I know that some type of mapping table would be needed but how would I return the ‘content’ field from both tables in one results column?

Thanks,
Steve

Ok so Union will join the two tables into one returned column, so it was a dumb question.

However, can you recommend how to specify an order for the content returned? For example would I use a separate mapping/order table like:

CREATE TABLE  `db`.`content_order` (
  `id` int(11) NOT NULL auto_increment,
  `content_id` int(11) NOT NULL,
  `content_table` varchar(100) NOT NULL default 'large_content',
  `member_id` int(11) NOT NULL,
  `order` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


INSERT INTO `db`.`content_order` VALUES  (1,2,'large_content',13746,3),
 (2,1,'large_content',13746,2),
 (4,1,'content',13746,1);

And then would I Inner Join to get the order defined?

Steve

So I added an ‘content_order’ field to both tables and then can order by content_order.

My query:

select c.content
     , c.content_order
from
    content as c
Where
    c.member_id = '14567'
union 
    select lc.content
         , lc.content_order
    from
        large_content as lc
    where
        lc.member_id = '14567'
order by content_order

I don’t like that I have the order by info split between the two tables but it is a much easier solution that trying to figure out a way to have a single mapping table for the order.

Please accept my apologies for rambling - maybe I should have spend 10 more minutes looking before I started this thread :wink:

Steve