Get Records By Most Recent Date From two tables

Question

I have two SQL tables. Each has an ID with other columns and a Date.

Is there a way that I can get the result from these two tables in one query sorted by the date? For example, as a result, I may have one record from table 1 followed by two records from table 2 and then another record from table one and so on. I have tried the code below but I think that I am not on the right track.

I would appreciate your help.

SELECT
    app.ID as 'AppraisalID',
    app.CityName,
    app.CountryName,
    app.Street,
    app.DateCreated,
    subApp.ID as 'SubAppraisalID',
    subApp.Message,
    subApp.DateCreated
From
(
    SELECT TOP 10
        dbo.Appraisal.ID,
        dbo.Appraisal.Street,
        dbo.Country.Name as 'CountryName',
        dbo.City.Name as 'CityName',
        dbo.Appraisal.DateCreated
    FROM dbo.Appraisal
    INNER JOIN dbo.Country ON dbo.Appraisal.CountryID = dbo.Country.ID
    INNER JOIN dbo.City ON dbo.Appraisal.CityID = dbo.City.ID
    Order by dbo.Appraisal.DateCreated DESC
) app
Cross Join
(
    SELECT TOP 10
        dbo.Sub_Appraisal.ID,
        dbo.Sub_Appraisal.Message,
        dbo.Sub_Appraisal.DateCreated
    FROM dbo.Sub_Appraisal
    Order by dbo.Sub_Appraisal.DateCreated DESC
) subApp
Order By
    app.DateCreated DESC,
    subApp.DateCreated DESC

Thanks guys.

Answer 1

Look at union all. You'll need to make sure that your result columns are the same data type.

select a.id "id", null "message", a.cityname "city", a.countryname "country", a.street "street", a.datecreated "dt"
from dbo.appraisal a
union all
select s.id, s.message, null, null, null, s.datecreated
from dbo.sub_appraisal s
order by 6

However, I suspect that your sub_appraisal table is missing an ID linking it to the appraisal table. This is how you would ideally join the two tables allowing you to accurately get the data out, in the correct order because you cannot guarantee that sub_appraisal records are created directly after appraisal records and before another appraisal record is created. If this happened, your query would give you results you're possibly not expecting.

Answer 2

What you want to use is the UNION operator, although the column lists for each table (or at least the ones that you are selecting) must match up. You'll want to make sure that you do the ordering after the UNION.

A simplified example:

SELECT
    col1,
    col2,
    some_date
FROM
(
    SELECT
        col1,
        col2,
        some_date
    FROM
        Table1
    UNION ALL
    SELECT
        col1,
        col2,
        some_date
    FROM
        Table2
) AS SQ
ORDER BY
    some_date