[SOLVED]Merge two tables into temporary table or recordset

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

[SOLVED]Merge two tables into temporary table or recordset

Post by Skittlewidth »

I'm having a little trouble figuring out how to do the following:

I have two tables that are pretty similar in structure but have different column names - a 'news_articles' table, and a 'case_studies' table.
Both have columns for date, title and body content but these are called news_date, news_title, news_article, and cs_date, cs_title and casestudy respectively. In the case_studies table amongst other things there is a column that denotes whether a case_study can count as a news article as well.

In the 'latest news' list on the website I want to be able to list the most recent 5 articles by date, and this needs to include the casestudys where applicable.
So far in my simple experiments I've tried to give the column names the same aliases to try and return a single column of results (never mind whether the casestudy is ticked as a news article at the moment) , i.e

Code: Select all

Simple example:
select case_study.cs_title as title, news_articles.news_title as title from case_study, news_articles;
but this returns 2 columns with the same name.

I think what I want is a temporary table with date, title and article columns, but I still can't figure out how to insert the data from both tables into the temporary table in one select query without causing that duplication. Can I use two?
Last edited by Skittlewidth on Wed Jan 18, 2006 6:51 am, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

select 
   cs_title as title
from case_study
UNION
select
   news_title as title
from
   news_articles;
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post by Skittlewidth »

Excellent thanks.

Never had to use a union before.
Post Reply