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

I need to write a TSQL procedure which would perform 4 selects and join results int 开发者_Go百科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

---

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

---

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.

---

Use temporary table

---

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

---

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

---

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

---

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