TSQL procedure which would perform multiple selects and join them into one result

Question

I need to write a TSQL procedure which would perform 4 selects and join results int one dataset. Can this be done?

P.S.

Selects are performed in a loop

EDIT

Here is the schema. I wan't to get N latest points from each Unit which belongs to SOME map.

Maps
   Id

Maps2Units
   MapId
   UnitId

Units
   Id

Points
   Id
   UnitId
   Timestamp

Answer 1

Use temporary table

Answer 2

rather than select in loops, I'd first try to refactor it into a single select to pull back all the information.

Outside of that, dump all the select information into a temporary table (or a table variable, depending on performance impact) and then select from that table to return all the information is one statement.

Answer 3

select col1 from tableA
union
select col2 from tableB
union
select col3 from tableC
union
select col4 from tableD

The columns selected must match datatype

Answer 4

If all 4 datasets share common values to link them into rows, use INNER JOIN.

If all 4 datasets have the same column definitions, but just retrieve different data, use UNION

EDIT - based on your updated question, sounds like you need something like the following: http://allenbrowne.com/subquery-01.html#TopN to get top N per group

Answer 5

Declare a temp (#) table or a table variable (@) and select into it. then just select * from your temp when you are done.

Answer 6

Edit: another solution using top

SELECT
    *
FROM
    Maps2Units MU
    CROSS APPLY
    (
    SELECT TOP (@n) --or TOP 3 etc
        *
    FROM
        Points P
    WHERE
        P.UnitId = MU.UnitId
    ) P2

Guessing from your previous comment...

;WITH myCTE AS 
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY P.UnitId ORDER BY P.Timestamp DESC) AS PointsRank
    FROM
        Points P
        JOIN
        Maps2Units MU ON P.UnitId = MU.UnitId
)
SELECT
    *
FROM
    myCTE
WHERE
    PointsRank <= @M;

Original:

;WITH myCTE AS 
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY SpeciesID ORDER BY AnimalID /* ? */) AS AnimalRank,
        DENSE_RANK() OVER (ORDER BY SpeciesID /* ? */) AS SpeciesRank
    FROM
        MyAnimals
)
SELECT
    *
FROM
    myCTE
WHERE
    SpeciesRank <= @M
    AND
    AnimalRank <= @N

Answer 7

From the information you provided:

SELECT Id, NULL AS MapID, NULL AS UnitID, NULL AS Timestamp FROM Maps

union all

SELECT NULL AS ID, MapID, UnitID, NULL AS Timestamp FROM Maps2Units

union all

SELECT ID, NULL, NULL, NULL FROM Units

union all 

SELECT id, null AS MapID, UnitID, null AS Timestamp FROM Points