Solved

multiple inner join sql statement

Posted on 2013-01-18
5
480 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 39

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now