query
I have two Table MATCH_MASTER and TEAM_MASTER
|---------------------------|
|M_ID | TEAM1_ID | TEAM2_ID |
|---------------------------|
| 1 | 1 | 2 |
|T_ID | T_NAME |
|--------------|
| 1 | A |
| 2 | B |
I want to select M_ID, team1 name and team2 name.
My query result should look like this:
|------------------------|
|M_ID | t1_Name | t2Name |
|------------------------|
| 1 | A | B |
How can I achieve this?
Something like:
select ct_id, sum(correct) as num_correct, sum(1-correct) as num_incorrect
from your_view group by ct_id;
may be the kind of thing you are looking for.
I cannot think of a reasonable way to meet the requirement with a T-SQL non-cursor solution, so the following is a SQLCLR procedure that returns a result set. The solution performs a single scan of each table (at most). In a test with 500,000 Payment rows and 1,000,000 Ledger rows, it completed in 4.8 seconds with a warm buffer pool and 5.2 seconds from a cold start - including the time to display the 120,000 rows of results in SSMS.
The procedure uses the EXTERNAL_ACCESS permission set, so ALTER DATABASE [name] SET TRUSTWORTHY ON; is the easiest way for testing (the database must also have a valid owner). For deployment, you might prefer to sign it and grant the permission explicitly. I was unable to upload this to SQL Fiddle as it has a limit of 8000 characters for DDL statements.
CREATE ASSEMBLY [dbase]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300A1AE59500000000000000000E00002210B0108000014000000060000000000000E320000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000BC3100004F000000004000009802000000000000000000000000000000000000006000000C000000303100001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014120000002000000014000000020000000000000000000000000000200000602E7273726300000098020000004000000004000000160000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001A00000000000000000000000000004000004200000000000000000000000000000000F0310000000000004800000002000500A8240000880C000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300600C603000001000011730500000A0A7201000070730600000A0B076F0700000A723500007007730800000A0C086F0900000A72AF0000701F0C20800000006F0A00000A186F0B00000A086F0900000A72C70000701F0C20800000006F0A00000A186F0B00000A086F0C00000A2606166F0D00000A06176F0E00000A06086F0900000A166F0F00000A6F1000000A74110000016F1100000A06086F0900000A176F0F00000A6F1000000A74110000016F1200000ADE0A072C06076F1300000ADC066F1400000A730600000A0D066F1400000A730600000A1304096F0700000A11046F0700000A72E300007009730800000A130572DE0200701104730800000A13061105202C0100006F1500000A1106202C0100006F1500000A1B8D14000001131311131672C10400701E731600000AA211131772D50400701E731600000AA211131872E704007019176A731700000AA211131972F90400701C731600000AA211131A72070500701F161F146A731700000AA2111313071107731800000A1308281900000A11086F1A00000A11056F1B00000A130911066F1B00000A130A110A6F1C00000A39E601000011096F1C00000A39DA010000110A166F1D00000A130B110A176F1D00000A130C110A186F1E00000A130D110A196F1F00000A130E1109166F1D00000A130F1109176F1D00000A13101109186F1F00000A13111109196F1E00000A1312110C1110400F010000110E1111357E110816110F6F2000000A110817110B6F2000000A110818110D6F2100000A110819110E6F2200000A11081A11126F2100000A281900000A11086F2300000A1111110E591311110A6F1C00000A392A010000110A166F1D00000A130B110A176F1D00000A130C110A186F1E00000A130D110A196F1F00000A130E3873FFFFFF11112300000000000000003645110816110F6F2000000A110817110B6F2000000A110818110D6F2100000A11081911116F2200000A11081A11126F2100000A281900000A11086F2300000A110E111159130E11096F1C00000A399F0000001109166F1D00000A130F1109176F1D00000A13101109186F1F00000A13111109196F1E00000A131238E8FEFFFF1110110C2F3611096F1C00000A2C631109166F1D00000A130F1109176F1D00000A13101109186F1F00000A13111109196F1E00000A131238ACFEFFFF110A6F1C00000A2C2D110A166F1D00000A130B110A176F1D00000A130C110A186F1E00000A130D110A196F1F00000A130E3876FEFFFF281900000A6F2400000ADE0C110A2C07110A6F1300000ADCDE0C11092C0711096F1300000ADCDE0C11042C0711046F1300000ADCDE0A092C06096F1300000ADC2A0000417C000002000000110000009B000000AC0000000A000000000000000200000093010000FE010000910300000C00000000000000020000008A010000150200009F0300000C0000000000000002000000CF000000DE020000AD0300000C0000000000000002000000C2000000F9020000BB0300000A000000000000001E02282500000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000004C020000237E0000B8020000A403000023537472696E6773000000005C06000020050000235553007C0B00001000000023475549440000008C0B0000FC00000023426C6F620000000000000002000001471402000900000000FA25330016000001000000190000000200000002000000250000000400000001000000010000000200000000000A00010000000000060035002E000600680055000B007C0000000600AB008B000600CB008B000A001601FB000A0042012C010A005D012C010A007E016B010A0090012C010A009B012C010A00C1012C010A00CE01EF000A00DC016B010A00E801EF000A0009026B01060058022E00060081022E000A0095026B010A00D702FB000A00E302FB000A00F102FB000A00FC02FB000A001E032C010A003A036B010000000001000000000001000100010010001400000005000100010050200000000096003C000A000100A0240000000086184F000E00010011004F00120021004F00180029004F000E0031004F000E0039004F000E0041004F00220049008B010E0051004F0027005100B2012E005900D80133007100FB013C0081001302420039002302460039002E024600590045024B0071004E02510039005F02220039006E02220091008D020E009900AF0255008100C4021800A1004F005900A1004F006000A9004F006800B10004036F00B9000D03740051002C037A00C90047037F00C9004C038300C90055038800C9005F038D00A90069039200A90072039800A9007C039E00B90086037400B90095030E0009004F000E00200023001D002E000B00C9002E001300D2002E001B00DB00A400048000000000000000000000000000000000E900000002000000000000000000000001002500000000000200000000000000000000000100EF00000000000000003C4D6F64756C653E0064626173652E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A656374004D617463684C65646765725265636F726473002E63746F720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650064626173650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E537472696E674275696C6465720053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E640053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E004462436F6D6D616E6400457865637574654E6F6E5175657279007365745F456E6C697374007365745F496E74656772617465645365637572697479006765745F4974656D006765745F56616C756500537472696E67007365745F44617461536F75726365007365745F496E697469616C436174616C6F670049446973706F7361626C6500446973706F7365004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E67007365745F436F6D6D616E6454696D656F75740053716C4D657461446174610053716C446174615265636F72640053716C436F6E746578740053716C50697065006765745F506970650053656E64526573756C747353746172740053716C44617461526561646572004578656375746552656164657200446244617461526561646572005265616400476574496E74333200476574537472696E6700476574446F75626C6500536574496E74333200536574537472696E6700536574446F75626C650053656E64526573756C7473526F770053656E64526573756C7473456E6400003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D00200074007200750065000079530045004C00450043005400200040005300650072007600650072004E0061006D00650020003D002000400040005300450052005600450052004E0041004D0045002C0020004000440061007400610062006100730065004E0061006D00650020003D002000440042005F004E0041004D00450028002900001740005300650072007600650072004E0061006D006500001B4000440061007400610062006100730065004E0061006D0065000081F90D000A002000200020002000200020002000200020002000200020002000200020002000530045004C0045004300540020000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020005000610079006D0065006E007400490044002C0020000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000520065006300700074004E006F002C000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200041006D006F0075006E0074002C0020000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020005000610079004D006F0064006500540079007000650020000D000A002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E005000610079006D0065006E007400440065007400610069006C0020000D000A0020002000200020002000200020002000200020002000200020002000200020004F00520044004500520020004200590020000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020005000610079006D0065006E007400490044003B000081E10D000A002000200020002000200020002000200020002000200020002000200020002000530045004C004500430054000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020004C0065006400670065007200490044002C0020000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000520065006300700074004E006F002C0020000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000460075006E00640054007900700065002C0020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200041006D006F0075006E00740020000D000A002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E004C006500640067006500720020000D000A0020002000200020002000200020002000200020002000200020002000200020004F00520044004500520020004200590020000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020004C0065006400670065007200490044003B0000135000610079006D0065006E0074004900440000114C0065006400670065007200490044000011460075006E0064005400790070006500000D41006D006F0075006E00740000175000610079004D006F006400650054007900700065000000FF7A4814813C7D4BABD129FD442C46230008B77A5C561934E089030000010320000105200101110D04200101080401000000042001010E062002010E1221042000122D08200312310E11350805200101113D0320000804200101020520011231080320001C0320000E062002010E1135072003010E11350A062001011D1251040000125D05200101125504200012610320000204200108080420010E080420010D0805200201080805200201080E05200201080D240714121D1221122912211221122912291D125112551261126108080E0D08080D0E1D12510801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000A1AE59500000000002000000700000004C3100004C13000052534453FCB7D3C1C0B8134E9BE2C8123748E8CD09000000633A5C55736572735C5061756C2057686974655C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C53616E647069745C6F626A5C52656C656173655C64626173652E70646200E43100000000000000000000FE310000002000000000000000000000000000000000000000000000F0310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000003C02000000000000000000003C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0049C010000010053007400720069006E006700460069006C00650049006E0066006F0000007801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000034000A00010049006E007400650072006E0061006C004E0061006D0065000000640062006100730065002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000003C000A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000640062006100730065002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000103200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE PROCEDURE [dbo].[MatchLedgerRecords]
WITH EXECUTE AS CALLER AS
EXTERNAL NAME [dbase].[StoredProcedures].[MatchLedgerRecords];
Sample data reproduced from your SQL Fiddle:
CREATE TABLE dbo.PaymentDetail
(
PaymentId integer PRIMARY KEY,
RecptNo integer NOT NULL,
Amount float NOT NULL,
PayModeType varchar(20) NOT NULL
);
CREATE TABLE dbo.Ledger
(
LedgerId integer PRIMARY KEY,
Recptno integer NOT NULL,
FundType char(1) NOT NULL,
amount float NOT NULL
);
INSERT INTO dbo.PaymentDetail
(PaymentId, RecptNo, Amount, PayModeType)
VALUES
(1, 1, 1000, 'Cash'),
(2, 1, 3000, 'Cheque'),
(3, 2, 4000, 'Cheque'),
(4, 2, 1000, 'Cheque');
INSERT INTO dbo.Ledger
(LedgerId, Recptno, FundType, amount)
VALUES
(1, 1, 'R', 500),
(2, 1, 'A', 500),
(3, 1, 'I', 1000),
(4, 1, 'I', 1000),
(5, 1, 'I', 1000),
(6, 2, 'R', 2000),
(7, 2, 'A', 1000),
(8, 2, 'I', 2000);
Usage:
EXECUTE [dbo].[MatchLedgerRecords];
Output:
Source code:
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void MatchLedgerRecords()
{
SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();
// Use a context connection to find the name of the server and current database
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT @ServerName = @@SERVERNAME, @DatabaseName = DB_NAME()", conn);
cmd.Parameters.Add("@ServerName", SqlDbType.NVarChar, 128).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@DatabaseName", SqlDbType.NVarChar, 128).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
// Build a connection string
scb.Enlist = false;
scb.IntegratedSecurity = true;
scb.DataSource = (string)cmd.Parameters[0].Value;
scb.InitialCatalog = (string)cmd.Parameters[1].Value;
}
// Non-context-connections
using (SqlConnection conn1 = new SqlConnection(scb.ConnectionString), conn2 = new SqlConnection(scb.ConnectionString))
{
conn1.Open();
conn2.Open();
// Commands to read the two tables in the required order
SqlCommand paymentCommand = new SqlCommand(
@"
SELECT
PaymentID,
RecptNo,
Amount,
PayModeType
FROM dbo.PaymentDetail
ORDER BY
PaymentID;", conn1);
SqlCommand ledgerCommand = new SqlCommand(
@"
SELECT
LedgerID,
RecptNo,
FundType,
Amount
FROM dbo.Ledger
ORDER BY
LedgerID;", conn2);
// Avoid default 30 second timeout
paymentCommand.CommandTimeout = 300;
ledgerCommand.CommandTimeout = 300;
// Shape of the results
SqlMetaData[] smd = new SqlMetaData[]
{
new SqlMetaData("PaymentID", SqlDbType.Int),
new SqlMetaData("LedgerID", SqlDbType.Int),
new SqlMetaData("FundType", SqlDbType.Char, 1),
new SqlMetaData("Amount", SqlDbType.Float),
new SqlMetaData("PayModeType", SqlDbType.VarChar, 20)
};
SqlDataRecord sdr = new SqlDataRecord(smd);
// Indicate start-of-results to SQL Server
SqlContext.Pipe.SendResultsStart(sdr);
// Main algorithm
using (SqlDataReader paymentReader = paymentCommand.ExecuteReader(), ledgerReader = ledgerCommand.ExecuteReader())
{
if (ledgerReader.Read() && paymentReader.Read())
{
// Read the first ledger row column values
// (ordinal numbers are the column position from the original SELECT)
int LedgerID = ledgerReader.GetInt32(0);
int LedgerReceiptNo = ledgerReader.GetInt32(1);
string FundType = ledgerReader.GetString(2);
double LedgerAmount = ledgerReader.GetDouble(3);
// Read the first payment row column values
int PaymentID = paymentReader.GetInt32(0);
int PaymentReceiptNo = paymentReader.GetInt32(1);
double PaymentAmount = paymentReader.GetDouble(2);
string PayModeType = paymentReader.GetString(3);
// A modified merge join
while (true)
{
// Join on receipt number
if (LedgerReceiptNo == PaymentReceiptNo)
{
// If the current ledger amount can be allocated from the current payment
if (LedgerAmount <= PaymentAmount)
{
// Set result column values
sdr.SetInt32(0, PaymentID);
sdr.SetInt32(1, LedgerID);
sdr.SetString(2, FundType);
sdr.SetDouble(3, LedgerAmount);
sdr.SetString(4, PayModeType);
// Send a result row
SqlContext.Pipe.SendResultsRow(sdr);
// Subtract the allocated amount from the payment amount available
PaymentAmount -= LedgerAmount;
// Next ledger record
if (!ledgerReader.Read()) break;
// Read the ledger record's columns
LedgerID = ledgerReader.GetInt32(0);
LedgerReceiptNo = ledgerReader.GetInt32(1);
FundType = ledgerReader.GetString(2);
LedgerAmount = ledgerReader.GetDouble(3);
}
// Ledger amount is greater than the payment amount remaining
else
{
// Return a row for the remaining payment amount (if non-zero)
if (PaymentAmount > 0)
{
sdr.SetInt32(0, PaymentID);
sdr.SetInt32(1, LedgerID);
sdr.SetString(2, FundType);
sdr.SetDouble(3, PaymentAmount);
sdr.SetString(4, PayModeType);
SqlContext.Pipe.SendResultsRow(sdr);
// Adjust the ledger amount
LedgerAmount -= PaymentAmount;
}
// Read the next payment record
if (!paymentReader.Read()) break;
// Read the payment record columns
PaymentID = paymentReader.GetInt32(0);
PaymentReceiptNo = paymentReader.GetInt32(1);
PaymentAmount = paymentReader.GetDouble(2);
PayModeType = paymentReader.GetString(3);
}
}
// Receipt numbers do not currently match, payment behind ledger
else if (PaymentReceiptNo < LedgerReceiptNo)
{
// Read next payment record
if (!paymentReader.Read()) break;
// Read column values
PaymentID = paymentReader.GetInt32(0);
PaymentReceiptNo = paymentReader.GetInt32(1);
PaymentAmount = paymentReader.GetDouble(2);
PayModeType = paymentReader.GetString(3);
}
// Receipt numbers do not currently match, ledger behind payment
else
{
// Read next ledger record
if (!ledgerReader.Read()) break;
// Read column values
LedgerID = ledgerReader.GetInt32(0);
LedgerReceiptNo = ledgerReader.GetInt32(1);
FundType = ledgerReader.GetString(2);
LedgerAmount = ledgerReader.GetDouble(3);
}
// end while: Repeat until ledger or payment runs out of rows
}
}
// End of result set
SqlContext.Pipe.SendResultsEnd();
}
}
}
}
Best Answer
Try following.