Solved

multiple inner join sql statement

Posted on 2013-01-18
5
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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