Page 1 of 1

[SOLVED]Merge two tables into temporary table or recordset

Posted: Wed Jan 18, 2006 6:05 am
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?

Posted: Wed Jan 18, 2006 6:37 am
by Weirdan

Code: Select all

select 
   cs_title as title
from case_study
UNION
select
   news_title as title
from
   news_articles;

Posted: Wed Jan 18, 2006 6:50 am
by Skittlewidth
Excellent thanks.

Never had to use a union before.