multiple inner join sql statement

I have this sql query which returns 3 columns of data.

I would like to create a new column in the same statement, and say...

grab targetColumn_value in OTHER_DB WHERE OTHER_DB.[Account_Number] =
[Account_Number] AND OTHER_DB.[targercolumnTWO] = 'testvalue'

I'm guessing its some kind of inner join... but I'm lost on how to add it to my current statement.

thanks in advance for the assistance
I hope my question is clear enough


SELECT finalTable.Account_Number, SUM(finalTable.TotalMarginUsage) AS TotalMarginUsage, SUM(finalTable.TotalPLasofSettle) AS TotalPLasofSettle
FROM
	(
	SELECT newTable.[Account_Number], newTable.BloombergSymbol, SUM(newTable.[TopdayNet]) AS NetPosition, ABS(SUM(newTable.[TotalMarginUsage])) AS [TotalMarginUsage], SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle
	FROM
		(
		SELECT dbo.TopdayTradesDB.Account_Number, dbo.TopdayTradesDB.BloombergSymbol, SUM(dbo.TopdayTradesDB.Qty_Net) As TopdayNet, SUM(dbo.TopdayTradesDB.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.TopdayTradesDB.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.TopdayTradesDB
		GROUP BY [Account_Number], BloombergSymbol, [Account_ID]
		UNION ALL
		SELECT dbo.DailyPositions_JEFF.Account_Number, dbo.DailyPositions_JEFF.BloombergSymbol, SUM(dbo.DailyPositions_JEFF.Qty_Net) As TopdayNet, SUM(dbo.DailyPositions_JEFF.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.DailyPositions_JEFF.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.DailyPositions_JEFF
		GROUP BY [Account_Number], BloombergSymbol, [Account_ID]
		) newTable
	GROUP BY newTable.[Account_Number], newTable.BloombergSymbol
	) finalTable
INNER JOIN dbo.AccountsDB
ON dbo.AccountsDB.Account_Number = finalTable.Account_Number

--INNER JOIN dbo.LedgerDB
--ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID

WHERE Client_ID = '418BB7B8-0180-4A5A-99F6-CE52E60E694F'
GROUP BY finalTable.Account_Number

Open in new window

solarissfAsked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

To see what is happening, take the subquery
(
SELECT dbo.LedgerDB.Journal_Amount
FROM dbo.LedgerDB
WHERE dbo.LedgerDB.Account_ID = finalTable.Account_ID
AND
dbo.LedgerDB.Journal_Type = 'NLV'
)

and make it a derived table and join it to the other tables, and then add LedgerDBJournal_Amount to the results set.

HTH
  David
0
 
lcohanDatabase AnalystCommented:
--Something like:
SELECT finalTable.Account_Number, SUM(finalTable.TotalMarginUsage) AS TotalMarginUsage, SUM(finalTable.TotalPLasofSettle) AS TotalPLasofSettle,
(SELECT targetColumn_value FROM OTHER_DB..Other_Table WHERE OTHER_DB..Other_Table.[Account_Number] = finalTable.Account_Number AND OTHER_DB..Other_Table.[targercolumnTWO] = 'testvalue')
FROM
      (
      SELECT newTable.[Account_Number], newTable.BloombergSymbol, SUM(newTable.[TopdayNet]) AS NetPosition, ABS(SUM(newTable.[TotalMarginUsage])) AS [TotalMarginUsage], SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle
      FROM
            (
            SELECT dbo.TopdayTradesDB.Account_Number, dbo.TopdayTradesDB.BloombergSymbol, SUM(dbo.TopdayTradesDB.Qty_Net) As TopdayNet, SUM(dbo.TopdayTradesDB.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.TopdayTradesDB.[P&L Settlement]) AS [P&LasofSettle]
            FROM dbo.TopdayTradesDB
            GROUP BY [Account_Number], BloombergSymbol, [Account_ID]
            UNION ALL
            SELECT dbo.DailyPositions_JEFF.Account_Number, dbo.DailyPositions_JEFF.BloombergSymbol, SUM(dbo.DailyPositions_JEFF.Qty_Net) As TopdayNet, SUM(dbo.DailyPositions_JEFF.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.DailyPositions_JEFF.[P&L Settlement]) AS [P&LasofSettle]
            FROM dbo.DailyPositions_JEFF
            GROUP BY [Account_Number], BloombergSymbol, [Account_ID]
            ) newTable
      GROUP BY newTable.[Account_Number], newTable.BloombergSymbol
      ) finalTable
INNER JOIN dbo.AccountsDB
ON dbo.AccountsDB.Account_Number = finalTable.Account_Number

--INNER JOIN dbo.LedgerDB
--ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID

WHERE Client_ID = '418BB7B8-0180-4A5A-99F6-CE52E60E694F'
GROUP BY finalTable.Account_Number
0
 
solarissfAuthor Commented:
this is what I changed it too

SELECT finalTable.Account_ID, finalTable.Account_Number, SUM(finalTable.TotalMarginUsage) AS TotalMarginUsage, SUM(finalTable.TotalPLasofSettle) AS TotalPLasofSettle,

(
SELECT dbo.LedgerDB.Journal_Amount 
FROM dbo.LedgerDB
WHERE dbo.LedgerDB.Account_ID = finalTable.Account_ID
AND
dbo.LedgerDB.Journal_Type = 'NLV'
) 

FROM
	(
	SELECT newTable.Account_ID, newTable.[Account_Number], newTable.BloombergSymbol, SUM(newTable.[TopdayNet]) AS NetPosition, ABS(SUM(newTable.[TotalMarginUsage])) AS [TotalMarginUsage], SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle
	FROM
		(
		SELECT dbo.TopdayTradesDB.Account_ID, dbo.TopdayTradesDB.Account_Number, dbo.TopdayTradesDB.BloombergSymbol, SUM(dbo.TopdayTradesDB.Qty_Net) As TopdayNet, SUM(dbo.TopdayTradesDB.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.TopdayTradesDB.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.TopdayTradesDB
		GROUP BY [Account_ID], [Account_Number], BloombergSymbol
		UNION ALL
		SELECT dbo.DailyPositions_JEFF.Account_ID, dbo.DailyPositions_JEFF.Account_Number, dbo.DailyPositions_JEFF.BloombergSymbol, SUM(dbo.DailyPositions_JEFF.Qty_Net) As TopdayNet, SUM(dbo.DailyPositions_JEFF.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.DailyPositions_JEFF.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.DailyPositions_JEFF
		GROUP BY [Account_ID], [Account_Number], BloombergSymbol
		) newTable
	GROUP BY newTable.Account_ID, newTable.[Account_Number], newTable.BloombergSymbol
	) finalTable
INNER JOIN dbo.AccountsDB
ON dbo.AccountsDB.Account_Number = finalTable.Account_Number

--INNER JOIN dbo.LedgerDB
--ON dbo.AccountsDB.Account_ID = dbo.LedgerDB.Account_ID

WHERE Client_ID = '418BB7B8-0180-4A5A-99F6-CE52E60E694F'
GROUP BY finalTable.Account_ID, finalTable.Account_Number

Open in new window


here is my error message

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


and I am also attaching a picture of the dbo.Ledger so you can see what I'm trying to grab...
I'm trying to make a column .. that brings in the JOURNAL_AMOUNT, only if the JOURNAL_TYPE is ='NLV'


then when that works I want another column... same thing that fills in data where JOURNAL_TYPE is 'MGN'

not sure where I'm going wrong.
sample.xlsx
0
 
solarissfAuthor Commented:
so I took the subquery and found out what was happening.  I forgot to put in one more WHERE clause which made it work successfully!

thank you!

now I want to make another column, which just subtracts the result from this subquery from another column in the select.

my error message is
Msg 207, Level 16, State 1, Line 22
Invalid column name 'NetLiqVal'.

new code
SELECT finalTable.Account_Number, 
(
SELECT dbo.LedgerDB.Journal_Amount 
FROM dbo.LedgerDB
WHERE dbo.LedgerDB.Account_ID = finalTable.Account_ID
AND
dbo.LedgerDB.Journal_Type = 'NLV'
AND dbo.LedgerDB.Journal_Date = '20130117'
) AS [NetLiqVal]
,
(
SELECT dbo.LedgerDB.Journal_Amount 
FROM dbo.LedgerDB
WHERE dbo.LedgerDB.Account_ID = finalTable.Account_ID
AND
dbo.LedgerDB.Journal_Type = 'MGN'
AND dbo.LedgerDB.Journal_Date = '20130117'
) AS [InitMgn]
,
SUM(finalTable.TotalMarginUsage) AS TotalMarginUsage, SUM(finalTable.TotalPLasofSettle) AS TotalPLasofSettle

, [NetLiqVal] - [TotalPLasofSettle] AS caclll

FROM
	(
	SELECT newTable.Account_ID, newTable.[Account_Number], newTable.BloombergSymbol, SUM(newTable.[TopdayNet]) AS NetPosition, ABS(SUM(newTable.[TotalMarginUsage])) AS [TotalMarginUsage], SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle
	FROM
		(
		SELECT dbo.TopdayTradesDB.Account_ID, dbo.TopdayTradesDB.Account_Number, dbo.TopdayTradesDB.BloombergSymbol, SUM(dbo.TopdayTradesDB.Qty_Net) As TopdayNet, SUM(dbo.TopdayTradesDB.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.TopdayTradesDB.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.TopdayTradesDB
		GROUP BY [Account_ID], [Account_Number], BloombergSymbol
		UNION ALL
		SELECT dbo.DailyPositions_JEFF.Account_ID, dbo.DailyPositions_JEFF.Account_Number, dbo.DailyPositions_JEFF.BloombergSymbol, SUM(dbo.DailyPositions_JEFF.Qty_Net) As TopdayNet, SUM(dbo.DailyPositions_JEFF.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.DailyPositions_JEFF.[P&L Settlement]) AS [P&LasofSettle]
		FROM dbo.DailyPositions_JEFF
		GROUP BY [Account_ID], [Account_Number], BloombergSymbol
		) newTable
	GROUP BY newTable.Account_ID, newTable.[Account_Number], newTable.BloombergSymbol
	) finalTable
INNER JOIN dbo.AccountsDB
ON dbo.AccountsDB.Account_Number = finalTable.Account_Number
WHERE Client_ID = '418BB7B8-0180-4A5A-99F6-CE52E60E694F'
GROUP BY finalTable.Account_ID, finalTable.Account_Number

Open in new window


any ideas? should I put this in a new POST?
0
 
David ToddSenior DBACommented:
Hi,

Thanks for the grade!

Yip, new question, new post.

HTH
  David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.