StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you don't mind having <strong>both paired transactions on the same row in the results</strong> rather than separate rows, then you could create each set as derived tables, named for example <code>a</code> & <code>b</code> below. Then <a href="http://en.wikipedia.org/wiki/Join_%28SQL%29#Cross_join" rel="nofollow"><code>CROSS JOIN</code></a> them like this...</p> <pre><code>SELECT a.SecurityID SecurityID_a, a.PortfolioCode PortfolioCode_a, a.SymbolNameSpace SymbolNameSpace_a, a.Symbol Symbol_a, a.TradeAmount TradeAmount_a, a.tradedate TradeDate_a, a.settledate SettleDate_a, a.PostDate PostDate_a, a.TransactionCode TransactionCode_a, a.SecurityID1 SecurityID1_a, b.SecurityID SecurityID_b, b.PortfolioCode PortfolioCode_b, b.SymbolNameSpace SymbolNameSpace_b, b.Symbol Symbol_b, b.TradeAmount TradeAmount_b, b.tradedate TradeDate_b, b.settledate SettleDate_b, b.PostDate PostDate_b, b.TransactionCode TransactionCode_b, b.SecurityID1 SecurityID1_b FROM ( SELECT Sec.SecurityID, Port.PortfolioCode, Sec.SymbolNameSpace, Sec.Symbol, PT.TradeAmount, PT.tradedate, PT.settledate, PT.PostDate, PT.TransactionCode, PT.SecurityID1 FROM AdvApp.vPortfolioTransaction PT JOIN AdvApp.vSecurity Sec ON Sec.SecurityID = PT.SecurityID1 LEFT JOIN AdvApp.vPortfolio PORT ON Port.PortfolioID = PT.PortfolioID WHERE Sec.SecurityID = 4678 ) a CROSS JOIN ( SELECT Sec.SecurityID, Port.PortfolioCode, Sec.SymbolNameSpace, Sec.Symbol, PT.TradeAmount, PT.tradedate, PT.settledate, PT.PostDate, PT.TransactionCode, PT.SecurityID1 FROM AdvApp.vPortfolioTransaction PT JOIN AdvApp.vSecurity Sec ON Sec.SecurityID = PT.SecurityID1 LEFT JOIN AdvApp.vPortfolio PORT ON Port.PortfolioID = PT.PortfolioID WHERE Sec.SecurityID = 6 ) b WHERE ABS(a.TradeAmount - b.TradeAmount) <= 5 </code></pre> <ul> <li><a href="http://sqlfiddle.com/#!3/8c9f9/2" rel="nofollow"><strong><code>Click here to see it in action at SQL Fiddle</code></strong></a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload