UNION ALL with 4 tables

Question

I am really new with MSSQL and Full Stack development I want to perform UNION ALL with 4 different tables for a loan-savings management system The following Code is a stored procedure I have for the 4 tables im getting the error

Msg 207, Level 16, State 1, Procedure USP_GetLoanSavingsDetailsByLoanTakerId, Line 13 Invalid column name 'GetSavings'.

Msg 207, Level 16, State 1, Procedure USP_GetLoanSavingsDetailsByLoanTakerId, Line 13 Invalid column name 'GiveSavings`

And this is my code I want to achieve the Loan Given to a person,Loan Received from a person, Savings Taken from a person and Savings Given to a person maintaining the transaction on the same day MemberId is the person I am taking the loan and giving the savings to and UserId is the person who wants to give the loan to the member and get/give savings to that same person

ALTER PROCEDURE [dbo].[USP_GetLoanSavingsDetailsByLoanTakerId]
(
    @MemberId bigint,
    @UserId bigint
)
AS
BEGIN
    
    --EXEC USP_GetLoanSavingsDetailsByLoanTakerId 5, 26


SELECT EntryDate, SUM(LoanGiven) AS LoanGiven, SUM(LoanReceived) AS LoanReceived , SUM(GetSavings) AS GetSavings, SUM(GiveSavings) AS GiveSavings
FROM(  
    SELECT EntryDate, TotalAmount as LoanGiven, 0 as LoanReceived
    FROM tblGiveLoan 
    WHERE UserId = @UserId and MemberId = @MemberId
    UNION ALL   
    SELECT EntryDate, 0 as LoanGiven, Amount as LoanReceived
    FROM tblReceiveLoan
    WHERE UserId = @UserId and MemberId = @MemberId
    UNION ALL
    SELECT EntryDate, TotalAmount as GetSavings, 0 as GiveSavings
    FROM tblGetSavings 
    WHERE UserId = @UserId and MemberId = @MemberId
    UNION ALL   
    SELECT EntryDate, 0 as GetSavings , TotalAmount as GiveSavings
    FROM tblGiveSavings
    WHERE UserId = @UserId and MemberId = @MemberId
) t
GROUP BY EntryDate
ORDER BY EntryDate ASC  
 END

Error messages claims that you use MS SQL aka SQL Server, not MySQL. The question is re-tagged. — Akina, Dec 30 '20 at 05:31
*im getting the error* In UNION the names of output rowset are taken from 1st subquery, names/aliases of all another subqueries are ignored. — Akina, Dec 30 '20 at 05:33
Please confirm whether its MySQL or SQL Server, and update your question (the first sentence and tags) accordingly. — Dale K, Dec 30 '20 at 09:48
@Akina I have another question can you help me please? https://stackoverflow.com/questions/65918586/automated-monthly-data-generation-in-sql — M_O_MEN, Jan 27 '21 at 12:28

score 0 · Answer 1 · answered Dec 30 '20 at 05:34

ALTER PROCEDURE [dbo].[USP_GetLoanSavingsDetailsByLoanTakerId]
(
    @MemberId bigint,
    @UserId bigint
)
AS
BEGIN
    
    --EXEC USP_GetLoanSavingsDetailsByLoanTakerId 5, 26


SELECT EntryDate, SUM(LoanGiven) AS LoanGiven, SUM(LoanReceived) AS LoanReceived , SUM(GetSavings) AS GetSavings, SUM(GiveSavings) AS GiveSavings
FROM(  
        SELECT EntryDate, TotalAmount as LoanGiven, 0 as LoanReceived, 0 as GetSavings, 0 as GiveSavings
        FROM tblGiveLoan 
        WHERE UserId = @UserId and MemberId = @MemberId
    UNION ALL   
        SELECT EntryDate, 0, Amount, 0, 0
        FROM tblReceiveLoan
        WHERE UserId = @UserId and MemberId = @MemberId
    UNION ALL
        SELECT EntryDate, 0, 0, TotalAmount, 0
        FROM tblGetSavings 
        WHERE UserId = @UserId and MemberId = @MemberId
    UNION ALL   
        SELECT EntryDate, 0, 0, 0, TotalAmount
        FROM tblGiveSavings
        WHERE UserId = @UserId and MemberId = @MemberId
    ) t
GROUP BY EntryDate
ORDER BY EntryDate ASC  
END

thank you It worked perfectly fine Thank you so much – M_O_MEN Dec 30 '20 at 07:15 — M_O_MEN, Dec 30 '20 at 07:15

UNION ALL with 4 tables

1 Answers1