merge two queries and having no relation

Question

I am creating a stored procedure in SQL Server, where are 2 different queries select different records but having same table, and both will not have any relation in the query.

Here is my query

SELECT * 
FROM
    (SELECT TOP 3
         Players.Player_Name AS 'HomeTeamBatsman',
         PlayerScores.Bat_Runs As 'HomeTeamRuns'        
     FROM 
         PlayerScores
     INNER JOIN
         Players ON PlayerScores.PlayerId = Players.PlayerId
     INNER JOIN
         Teams ON Players.TeamId = Teams.TeamId
     INNER JOIN
         Matches ON PlayerScores.MatchId = Matches.MatchId
     WHERE 
         (Teams.TeamId = 1)
         AND (Matches.MatchId = 1025)
     GROUP BY 
         Players.Player_Name,
         PlayerScores.Bat_Runs
     ORDER BY
         MAX(Bat_Runs) DESC) As HomeTeamBatting,
    (SELECT TOP 3
         Players.Player_Name AS 'OpponentTeamBatsman',
         PlayerScores.Bat_Runs As 'OpponentTeamRuns'     
     FROM 
         PlayerScores
     INNER JOIN
         Players ON PlayerScores.PlayerId = Players.PlayerId
     INNER JOIN
         Teams ON Players.TeamId = Teams.TeamId
     INNER JOIN
         Matches ON PlayerScores.MatchId = Matches.MatchId
     WHERE 
         (Teams.TeamId = 3)
         AND (Matches.MatchId = 1025)
     GROUP BY 
         Players.Player_Name,
         PlayerScores.Bat_Runs
     ORDER BY
         MAX(Bat_Runs) DESC) AS OpponentTeamBatting
END
go

It returns the following results, however, I don't want any relation b/t those queries

What I am expecting:

Answer 1

At a pure guess, I think you want UNION ALL:

SELECT *
FROM (
    SELECT TOP 3,
           'Home' AS Team,
           P.Player_Name AS Batsman,
           PS.Bat_Runs As Runs  
    FROM PlayerScores PS
         JOIN Players P ON PS.PlayerId = P.PlayerId
         JOIN Teams T ON P.TeamId = T.TeamId
         JOIN Matches M  ON PS.MatchId = M.MatchId
    WHERE T.TeamId = 1
    AND M.MatchId = 1025
    GROUP BY P.Player_Name,
             PS.Bat_Runs
    ORDER BY Bat_Runs DESC --As the GROUP BY is on Bat_Runs, MAX isn't needed
                           --In truth,why is there even a GROUP BY with no aggregation?
) AS Home
UNION ALL
SELECT *
FROM (
    SELECT Top 3
           'Away' AS Team,
           P.Player_Name AS Batsman,
           PS.Bat_Runs As Runs
    FROM PlayerScores PS
         JOIN Players P ON PS.PlayerId = P.PlayerId
         JOIN Teams T ON P.TeamId = T.TeamId
         JOIN Matches M  ON PS.MatchId = M.MatchId

    WHERE T.TeamId = 3
      AND Matches.MatchId = 1025
    GROUP BY P.Player_Name,
             PS.Bat_Runs
    ORDER BY Bat_Runs DESC
    ) AS Away;

Edit: We now have an expected result set. I suspect that the GROUP BY isn't needed here. Why? Well the OP has no aggregation so it makes no sense for it to be there so I have removed it.

They state they don't have a relationship, but there is one, the relationship they want is on the ranking of the runs for both team. This therefore gives a query like:

WITH Home AS(
    SELECT P.Player_Name AS Batsman,
           PS.Bat_Runs As Runs,
           RANK() OVER (ORDER BY PS.Bat_Runs) AS RunsRank
    FROM PlayerScores PS
         JOIN Players P ON PS.PlayerId = P.PlayerId
         JOIN Teams T ON P.TeamId = T.TeamId
         JOIN Matches M  ON PS.MatchId = M.MatchId
    WHERE T.TeamId = 1
    AND M.MatchId = 1025),
Away AS (
    SELECT P.Player_Name AS Batsman,
           PS.Bat_Runs As Runs,
           RANK() OVER (ORDER BY PS.Bat_Runs) AS RunsRank
    FROM PlayerScores PS
         JOIN Players P ON PS.PlayerId = P.PlayerId
         JOIN Teams T ON P.TeamId = T.TeamId
         JOIN Matches M  ON PS.MatchId = M.MatchId
    WHERE T.TeamId = 3
      AND Matches.MatchId = 1025
    )
SELECT H.Batsman AS HomeBatsMan,
       H.Runs AS HomeBatsManRuns,
       A.Batsman AS AwayBatsMan,
       A.Runs AS AwayBatsManRuns
FROM Home H
     JOIN Away A ON H.RunsRank = A.RunsRank
WHERE H.RunsRank <= 3;