Selecting data from two tables and ordering by date

I have two tables with data and need to combine them and order by date. I have the below query but the union keeps giving me an error.

SELECT 

AssetTitle,
AssetDate,
AssetTeaser,
AssetLink

FROM pressAssets WHERE AssetType=1 ORDER 开发者_运维技巧BY AssetDate ASC

UNION ALL

SELECT

BlogTitle,
BlogDate,
BlogEntry,
BlogLink


FROM

blogTempTable ORDER BY BlogDate ASC, AssetDate ASC;

Can anyone help me with this?

Your code doesn't work as is. This will get you a result set, but the date fields all get merged together; you can't distinguish the source after the UNION is applied.

SELECT 
  Title,
  Date,
  Teaser,
  Link

FROM (
SELECT 

AssetTitle Title,
AssetDate Date,
AssetTeaser Teaser,
AssetLink Link

FROM pressAssets WHERE AssetType=1 

UNION ALL

SELECT

BlogTitle Title,
BlogDate Date,
BlogEntry Teaser,
BlogLink Link


FROM

blogTempTable) T

ORDER BY Date ASC;

You're only allowed one ORDER BY for the UNIONed result set. You can't order the two queries individually.

Use subquery:

select *
from
(
    SELECT 

    AssetTitle,
    AssetDate,
    AssetTeaser,
    AssetLink

    FROM pressAssets WHERE AssetType=1 

    UNION ALL

    SELECT

    BlogTitle,
    BlogDate,
    BlogEntry,
    BlogLink


    FROM

    blogTempTable
)a
ORDER BY AssetDate ASC;

I think:

Select * from 
(
SELECT AssetTitle as mtitle, AssetDate as mdate, 
AssetTeaser as mtease, AssetLink as mlink
FROM pressAssets 
WHERE AssetType=1
UNION
SELECT BlogTitle as mtitle, BlogDate as mdate,
BlogEntry as mtease, BlogLink, as mlink
FROM blogTempTable
)
ORDER BY mdate

would be close.

EDIT: This is the same approach as Yuck's corrected answer.