Solved

multiple inner join sql statement

Posted on 2013-01-18
5
484 Views
Last Modified: 2013-01-21
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

0
Comment
Question by:solarissf
  • 2
  • 2
5 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 38794856
--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
 

Author Comment

by:solarissf
ID: 38797453
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
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 38797673
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
 

Author Comment

by:solarissf
ID: 38802391
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
 
LVL 35

Expert Comment

by:David Todd
ID: 38802415
Hi,

Thanks for the grade!

Yip, new question, new post.

HTH
  David
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question