Post reply

Problem Using subquery and join

  • August 8, 2012 at 8:23 am

    #279343

    Dear All,

    I have a problem with my query. This is my table structure and some data. Actually there is 400.000 records on test_table1 and 100.000 record on test_table2.

    -- Structure for test_table1

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#test_table1','U') IS NOT NULL

    DROP TABLE #test_table1

    --===== Create the test table with

    CREATE TABLE #test_table1

    (

    ID VARCHAR(15) NOT NULL,

    AccountNumber VARCHAR(14) NOT NULL,

    InvType VARCHAR(100),

    FirstName VARCHAR(40),

    MiddleName VARCHAR(40),

    LastName VARCHAR(40),

    Sex CHAR,

    BirthDate DATETIME,

    Nationality VARCHAR(100)

    )

    ALTER TABLE #test_table1

    ADD CONSTRAINT pk_testtable1 PRIMARY KEY (ID,AccountNumber)

    GO

    INSERT INTO #test_table1

    ( ID ,

    AccountNumber ,

    InvType ,

    FirstName ,

    MiddleName ,

    LastName ,

    Sex ,

    BirthDate ,

    Nationality

    )

    SELECT 'IDD100211088647','AD001029100150','ID','Robert','','','','1965-02-10','India' UNION ALL

    SELECT 'IDD060210753675','AD001070800147','ID','Tomy Goretha','','','','1979-02-06','India' UNION ALL

    SELECT 'IDD291213785582','AD001088000166','ID','Andy','','','','1960-12-29','India' UNION ALL

    SELECT 'IDD171273376143','AD001117700152','ID','Elisabeth Risort','','','','1978-12-17','India' UNION ALL

    SELECT 'IDD150676045636','AD001144800134','ID','Lisbon','','','','1988-06-15','India' UNION ALL

    SELECT 'IDD140810826918','OD001Q01400177','ID','DEWI HARTANA','','','','1965-08-14','India' UNION ALL

    SELECT 'IDD250720871814','HP001024400163','ID','MELLIA SENJAYA','','','','1978-07-25','India' UNION ALL

    SELECT 'IDD150811181900','HP001058100185','ID','JEFFREY WINATA','','','','1981-08-15','India' UNION ALL

    SELECT 'IDD281211987560','HP001087300118','ID','HILMAN TASLIM','','','','1947-12-28','India' UNION ALL

    SELECT 'CPD070258721868','HP001128200166','CP','GEO LINK Inc.','','','','2006-02-07','India' UNION ALL

    SELECT 'IDD030212790091','HP001138200159','ID','Tony Liu','','','','1972-02-03','India' UNION ALL

    SELECT 'IDD111011542143','HP001153500190','ID','SULIANTO. DR','','','','1960-10-11','India' UNION ALL

    SELECT 'IDD221019714259','PC001084200115','ID','Thio Li Hong','','','','1964-10-22','India' UNION ALL

    SELECT 'IDD291035208792','KK001843800187','ID','Asing','','','','1959-10-29','India' UNION ALL

    SELECT 'IDD131152529026','KK001937800160','ID','Tjan Harmen','','','','1969-11-13','India' UNION ALL

    SELECT 'IDD301113785425','LG001016000118','ID','WILLY','','','','1962-11-30','India' UNION ALL

    SELECT 'IDD170810859747','YP001SLKS00191','ID','Justin','','','','1984-08-17','India' UNION ALL

    SELECT 'IDD031113718148','KS001438600172','ID','Romy','','','','1976-11-03','India' UNION ALL

    SELECT 'IDD200974783105','AI001T07000104','ID','Piter Tiendey','','','','1982-09-20','India' UNION ALL

    SELECT 'IDD240716430035','KI001514400112','ID','Jason','','','','1954-07-24','India' UNION ALL

    SELECT 'IDD010333270592','PI001082100167','ID','Anita Silvia','','','','1965-03-01','India' UNION ALL

    SELECT 'IDD211265224285','AZ001468300195','ID','Laita','','','','1966-12-21','India' UNION ALL

    SELECT 'IDD060210753675','KI001685100106','ID','TOMY GORETHA','','','','1979-02-06','India' UNION ALL

    SELECT 'IDD201211017802','NI001Z96800144','ID','WILLIYANTO RUSTARDY','','','','1981-12-20','India' UNION ALL

    SELECT 'IDD210210332908','II001107200169','ID','Lim Melie','','','','1972-02-21','India' UNION ALL

    SELECT 'IDD121157256963','KI001023900111','ID','Suryani Kurniawan','','','','1945-11-12','India' UNION ALL

    SELECT 'IDD101022266907','AO001019700100','ID','TIO HANDOKO PRASETYO','','','','1968-10-10','India' UNION ALL

    SELECT 'IDD010534829574','PD001252100191','ID','Adrie Limen','','','','1960-05-01','India' UNION ALL

    SELECT 'IDD161110364421','YJ001436600122','ID','William Sulaeman','','','','1965-11-16','India' UNION ALL

    SELECT 'IDD131022268377','AO001052300180','ID','JANLIS JULIANNI','','','','1939-10-13','India' UNION ALL

    SELECT 'IDD100921134637','GR001827100170','ID','JONY HALIM','','','','1978-09-10','India' UNION ALL

    SELECT 'IDD180913400152','OD001C12500122','ID','SLAMET KITA SINULINGA, DRS','','','','1942-09-18','India' UNION ALL

    SELECT 'IDD051021069254','GR001566400142','ID','BOND HAWANA','','','','1979-10-05','India' UNION ALL

    SELECT 'IDD040816849012','KI001360500114','ID','Joe Eng Hong/Johan','','','','1970-08-04','India' UNION ALL

    SELECT 'IDD060521189194','GR001912000129','ID','SLAMET IWAN SANTOSO','','','','1967-05-06','India' UNION ALL

    SELECT 'IDD170829068074','AI001282400143','ID','Endah Lestari','','','','1985-08-17','India' UNION ALL

    SELECT 'IDD070910456619','YP001J41200110','ID','ANDY TANUJAYA','','','','1964-09-07','India' UNION ALL

    SELECT 'IDD031210140608','CC001369000127','ID','EKA SUHENDRA','','','','1982-12-03','India' UNION ALL

    SELECT 'IDD221019714259','DH001283800179','ID','THIO LI HONG','','','','1964-10-22','India' UNION ALL

    SELECT 'IDD010711055519','KI001778100167','ID','TJIN DAVID JONG','','','','1982-07-01','India' UNION ALL

    SELECT 'IDD310310370253','CP001C83600186','ID','TEDDY DJOHAN','','','','1974-03-31','India' UNION ALL

    SELECT 'IDD190623118846','II001002000134','ID','Hengki Jananto','','','','1951-06-19','India' UNION ALL

    SELECT 'IDD220522182906','PG001006300124','ID','BUDY BUNTARAM','','','','1971-05-22','India' UNION ALL

    SELECT 'IDD041211604624','KI001414100189','ID','Harianto','','','','1972-12-04','India' UNION ALL

    SELECT 'IDD040634101228','YP001581500139','ID','VERONIKA HARYUNI SETYONINGSIH SE','','','','1975-06-04','India' UNION ALL

    SELECT 'IDD100310611990','MG001336800184','ID','Irvan Martius','','','','1986-03-10','India' UNION ALL

    SELECT 'IDD241226273385','FS001319200186','ID','Muhammad Zulkarnainirmm','','','','1953-12-24','India' UNION ALL

    SELECT 'IDD100211088647','OD0012CD200101','ID','SUBIANTO IRAWAN','','','','1965-02-10','India' UNION ALL

    SELECT 'IDD310718801712','PG001088600176','ID','Eka Namara Ginting','','','','1968-07-31','India' UNION ALL

    SELECT 'IDD270210768064','ID001TO0700105','ID','ONGGO IWAN KUSNADI','','','','1953-02-27','India' UNION ALL

    SELECT 'IDD190120904263','GR001852900150','ID','HENGKI SETIAWAN','','','','1953-01-19','India' UNION ALL

    SELECT 'IDD030121523612','GR001369900168','ID','LYDIA HERLIANTY W','','','','1953-01-03','India' UNION ALL

    SELECT 'IDD150839126309','PD001N74100193','ID','ELISA SANTI','','','','1985-08-15','India' UNION ALL

    SELECT 'IDD220751171639','PG001131800138','ID','Yuliana Hartono','','','','1980-07-22','India' UNION ALL

    SELECT 'IDD290910673445','DX001K01300114','ID','Agus Soejanto Phurwo','','','','1968-09-29','India' UNION ALL

    SELECT 'IDD261213404585','CD001A46900120','ID','ANDY PRABAJAYA','','','','1960-12-26','India' UNION ALL

    SELECT 'IDD210910516022','YP001SJWO00126','ID','SUNARYO SUGIANTO IR','','','','1956-09-21','India' UNION ALL

    SELECT 'IDD170513053063','DH001423200192','ID','DRS. JUSAK PRIATNA RAHAJU','','','','1969-05-17','India' UNION ALL

    SELECT 'IDD101112676801','PI001156000124','ID','INONG PRIYONO','','','','1961-11-10','India' UNION ALL

    SELECT 'IDD221221152736','CP001C71100122','ID','FERRY LESLIE MULIJONO','','','','1975-12-22','India' UNION ALL

    SELECT 'IDD160311199450','YU001170200184','ID','Budiman Gunawan','','','','1957-03-16','India' UNION ALL

    SELECT 'IDD180413806478','KI001357000131','ID','Firman Simanjuntak','','','','1963-04-18','India' UNION ALL

    SELECT 'IDD160274247817','PD001R09700187','ID','HANDY JOSEPH','','','','1986-02-16','India' UNION ALL

    SELECT 'IDD141111505092','NI001459800178','ID','TJANG PAK NING','','','','1958-11-14','India' UNION ALL

    SELECT 'IDD010534829574','IF001431400185','ID','ADRIE LIMEN','','','','1960-05-01','India' UNION ALL

    SELECT 'IDD170414903576','IN001MTO000109','ID','Hermanto','','','','1975-04-17','India' UNION ALL

    SELECT 'IDD180952380962','OD001G46000192','ID','GUNAWAN PERAJOGO','','','','1973-09-18','India' UNION ALL

    SELECT 'IDD090541968354','BZ001232500121','ID','Danar Samron','','','','1983-05-09','India' UNION ALL

    SELECT 'IDD230216671734','KI001700400137','ID','HETTY','','','','1967-02-23','India' UNION ALL

    SELECT 'IDD310712757382','KK001614600103','ID','Wahono Santoso','','','','1974-07-31','India' UNION ALL

    SELECT 'IDD080211049034','AI001527700110','ID','Yani Hermawan','','','','1951-02-08','India' UNION ALL

    SELECT 'IDD161013447250','CC001577100186','ID','VENNY YULIANI YAP','','','','1957-10-16','India' UNION ALL

    SELECT 'IDD100323130256','II001012300120','ID','Lay Susan Margareth','','','','1964-03-10','India' UNION ALL

    SELECT 'IDD260459747772','DH001430800144','ID','TASMAN','','','','1986-04-26','India' UNION ALL

    SELECT 'IDD280812738261','BS001017900194','ID','IR. JOSEPH MARTINUS','','','','1944-08-28','India' UNION ALL

    SELECT 'IDD220352691867','CP001424900171','ID','YOHAN WILYANTO','','','','1979-03-22','India' UNION ALL

    SELECT 'IDD181133917183','KK001477300138','ID','Christine Tejosukmono','','','','1976-11-18','India' UNION ALL

    SELECT 'IDD290916613071','KI001441700127','ID','Tijono Widjojo','','','','1953-09-29','India' UNION ALL

    SELECT 'IDD261056109047','AN001155700111','ID','V. SARI DEWI','','','','1977-10-26','India' UNION ALL

    SELECT 'IDD020111563203','IN001ALX000166','ID','Alexander Sugiman Tjioe','','','','1959-01-02','India' UNION ALL

    SELECT 'IDD040610765504','CP001261200143','ID','EFENDI HATMAJA','','','','1955-06-04','India' UNION ALL

    SELECT 'IDD190112727552','TP001063000121','ID','SANTOSO.','','','','1973-01-19','India' UNION ALL

    SELECT 'IDD050216473863','YP001SHFJ00137','ID','YOPIE NATA SURYO PUTRO ST','','','','1977-02-05','India' UNION ALL

    SELECT 'IDD020111563203','AO001057700151','ID','ALEXANDER SUGIMAN TJIOE','','','','1959-01-02','India' UNION ALL

    SELECT 'IDD270311546155','TF001W27500106','ID','RITA HERAWATI TATANG','','','','1974-03-27','India' UNION ALL

    SELECT 'IDD210511352207','SH001213300149','ID','Djonli Tamir','','','','1967-05-21','India' UNION ALL

    SELECT 'IDD070534037094','KK001299000192','ID','KRISTIANA CHENDRA','','','','1947-05-07','India' UNION ALL

    SELECT 'IDD051110230525','MG001312600102','ID','Lina Sutjipto','','','','1972-11-05','India' UNION ALL

    SELECT 'IDD270311546155','KK001321200159','ID','Rita Herawati Tatang','','','','1974-03-27','India' UNION ALL

    SELECT 'IDD250816880449','YP001SLX100173','ID','ANDREW BUDIMAN','','','','1964-08-25','India' UNION ALL

    SELECT 'IDD110910623702','PG001075000170','ID','Lenna Wikarta','','','','1968-09-11','India' UNION ALL

    SELECT 'IDD200946655179','KI001744000122','ID','EDWIN KUSWANTO','','','','1987-09-20','India' UNION ALL

    SELECT 'IDD200913395516','SQ001F32500120','ID','SUHAMAN','','','','1966-09-20','India' UNION ALL

    SELECT 'IDD131012752644','PF001115000190','ID','TATANG BUDY SANTOSO','','','','1968-10-13','India' UNION ALL

    SELECT 'IDD171210720489','AF001W02900104','ID','WATNI DJOHAN','','','','1936-12-17','India' UNION ALL

    SELECT 'IDD271118424291','CC001CY7A00101','ID','NOVIYAN HALIM','','','','1972-11-27','India' UNION ALL

    SELECT 'IDD031011952290','BS001175600168','ID','HENGKY CANDRA','','','','1953-10-03','India' UNION ALL

    SELECT 'IDD110140578811','AO001011900185','ID','MARNI M. HUTAPEA','','','','1968-01-11','India' UNION ALL

    SELECT 'IDD291220065756','AH001209300183','ID','PETER GEORGE ANDRE LAWA','','','','1969-12-29','India' UNION ALL

    SELECT 'IDD160113996506','YP001SEOW00139','ID','DAVID TANDRIS','','','','1983-01-16','India'

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#test_table2','U') IS NOT NULL

    DROP TABLE #test_table2

    --===== Create the test table with

    CREATE TABLE #test_table2(

    [Row] [varchar](255) NULL,

    [Type] [varchar](255) NULL,

    [BS] [varchar](255) NULL,

    [Filled] [varchar](255) NULL,

    [ReportTime] [varchar](255) NULL,

    [Date] [datetime] NULL,

    [Price] [varchar](255) NULL,

    [TransVol] [numeric](18, 0) NULL,

    [TransVal] [varchar](255) NULL,

    [Board] [varchar](255) NULL,

    [OrderId] [varchar](255) NULL,

    [TradeId] [varchar](255) NULL,

    [AskClient] [varchar](255) NULL,

    [AskPrice] [varchar](255) NULL,

    [AskPretradeVol] [varchar](255) NULL,

    [AskYield] [varchar](255) NULL,

    [AskFlags] [varchar](255) NULL,

    [BidOrderId] [varchar](255) NULL,

    [BidTimeEntered] [varchar](255) NULL,

    [BidBroker] [varchar](255) NULL,

    [BidTrader] [varchar](255) NULL,

    [BidClient] [varchar](255) NULL,

    [BidPrice] [varchar](255) NULL,

    [BidPretradeVol] [varchar](255) NULL,

    [BidYield] [varchar](255) NULL,

    )

    INSERT INTO #test_table2

    ( Row ,

    Type ,

    BS ,

    Filled ,

    ReportTime ,

    Date ,

    Price ,

    TransVol ,

    TransVal ,

    Board ,

    OrderId ,

    TradeId ,

    AskClient ,

    AskPrice ,

    AskPretradeVol ,

    AskYield ,

    AskFlags ,

    BidOrderId ,

    BidTimeEntered ,

    BidBroker ,

    BidTrader ,

    BidClient ,

    BidPrice ,

    BidPretradeVol ,

    BidYield

    )

    SELECT '2065','TRADE','Trd','','Jan 1 1900 10:18AM','Apr 12 2012 12:00AM','440','100000','44000000','Regular Board','-','153859260','237783','Rp440.00','x100,000','0%','RG','374735749','10:18:34','YU','YUTR1004','290473','440','2396000.00','0%' UNION ALL

    SELECT '3186','TRADE','Trd','','Jan 1 1900 10:05AM','Apr 13 2012 12:00AM','420','7500','3150000','Regular Board','-','154012853','237783','Rp420.00','x250,000','0%','RG','375065587','10:05:06','OD','ODTR1005','448719','420','7500.00','0%' UNION ALL

    SELECT '3187','TRADE','Trd','','Jan 1 1900 10:05AM','Apr 13 2012 12:00AM','420','15000','6300000','Regular Board','-','154012864','237783','Rp420.00','x242,500','0%','RG','375065608','10:05:07','YP','YPTR1008','173528','420','15000.00','0%' UNION ALL

    SELECT '3190','TRADE','Trd','','Jan 1 1900 10:14AM','Apr 13 2012 12:00AM','420','1000','420000','Regular Board','-','154018373','237783','Rp420.00','x227,500','0%','RG','375076569','10:14:32','PD','PDTR1004','137648','420','1000.00','0%' UNION ALL

    SELECT '2045','TRADE','Trd','','Jan 1 1900 10:18AM','Apr 12 2012 12:00AM','440','100000','44000000','Regular Board','-','153859240','237783','Rp440.00','x100,000','0%','RG','374735749','10:18:34','YU','YUTR1004','290473','440','2783000.00','0%' UNION ALL

    SELECT '3191','TRADE','Trd','','Jan 1 1900 10:29AM','Apr 13 2012 12:00AM','420','5000','2100000','Regular Board','-','154024980','237783','Rp420.00','x226,500','0%','RG','375090049','10:29:09','YP','YPTR1004','495874','420','5000.00','0%' UNION ALL

    SELECT '3192','TRADE','Trd','','Jan 1 1900 10:30AM','Apr 13 2012 12:00AM','420','12500','5250000','Regular Board','-','154025523','237783','Rp420.00','x221,500','0%','RG','375090915','10:30:04','YP','YPTR1007','173528','420','12500.00','0%' UNION ALL

    SELECT '3193','TRADE','Trd','','Jan 1 1900 10:30AM','Apr 13 2012 12:00AM','420','12500','5250000','Regular Board','-','154025630','237783','Rp420.00','x209,000','0%','RG','375091204','10:30:21','YP','YPTR1005','173528','420','12500.00','0%' UNION ALL

    SELECT '3194','TRADE','Trd','','Jan 1 1900 10:30AM','Apr 13 2012 12:00AM','420','13000','5460000','Regular Board','-','154025928','237783','Rp420.00','x196,500','0%','RG','375091629','10:30:44','YP','YPTR1006','173528','420','13000.00','0%' UNION ALL

    SELECT '3195','TRADE','Trd','','Jan 1 1900 10:31AM','Apr 13 2012 12:00AM','420','2500','1050000','Regular Board','-','154026640','237783','Rp420.00','x183,500','0%','RG','375092713','10:31:42','AI','AITR1001','747831','420','2500.00','0%' UNION ALL

    SELECT '3196','TRADE','Trd','','Jan 1 1900 10:31AM','Apr 13 2012 12:00AM','420','3000','1260000','Regular Board','-','154026654','237783','Rp420.00','x181,000','0%','RG','375092732','10:31:43','YP','YPTR1005','173528','420','3000.00','0%' UNION ALL

    SELECT '3198','TRADE','Trd','','Jan 1 1900 10:31AM','Apr 13 2012 12:00AM','420','5000','2100000','Regular Board','-','154026714','237783','Rp420.00','x178,000','0%','RG','375092817','10:31:48','YP','YPTR1001','331135','420','5000.00','0%' UNION ALL

    SELECT '3199','TRADE','Trd','','Jan 1 1900 10:32AM','Apr 13 2012 12:00AM','420','5000','2100000','Regular Board','-','154026938','237783','Rp420.00','x173,000','0%','RG','375093467','10:32:32','YP','YPTR1001','169331','420','5000.00','0%' UNION ALL

    SELECT '3200','TRADE','Trd','','Jan 1 1900 10:32AM','Apr 13 2012 12:00AM','420','500','210000','Regular Board','-','154027050','237783','Rp420.00','x168,000','0%','RG','375093625','10:32:45','YP','YPTR1004','173528','420','500.00','0%' UNION ALL

    SELECT '3201','TRADE','Trd','','Jan 1 1900 10:33AM','Apr 13 2012 12:00AM','420','500','210000','Regular Board','-','154027104','237783','Rp420.00','x167,500','0%','RG','375093841','10:33:03','PD','PDTR1006','102979','420','500.00','0%' UNION ALL

    SELECT '3203','TRADE','Trd','','Jan 1 1900 10:33AM','Apr 13 2012 12:00AM','420','500','210000','Regular Board','-','154027338','237783','Rp420.00','x167,000','0%','RG','375094319','10:33:44','DH','DHTR1001','769149','420','500.00','0%' UNION ALL

    SELECT '3204','TRADE','Trd','','Jan 1 1900 10:37AM','Apr 13 2012 12:00AM','420','5000','2100000','Regular Board','-','154029126','237783','Rp420.00','x166,500','0%','RG','375097953','10:37:26','NI','NITR1003','741218','420','5000.00','0%' UNION ALL

    SELECT '3205','TRADE','Trd','','Jan 1 1900 10:38AM','Apr 13 2012 12:00AM','420','161500','67830000','Regular Board','-','154029530','237783','Rp420.00','x161,500','0%','RG','375098904','10:38:41','CP','CPTR1001','116591','420','500000.00','0%' UNION ALL

    SELECT '1519','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','812500','337187500','Regular Board','-','153844940','148436','Rp415.00','x1,000,000','0%','RG','374705816','9:53:19','YU','YUTR1004','290473','415','812500.00','0%' UNION ALL

    SELECT '1520','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','1500','622500','Regular Board','-','153844941','148436','Rp415.00','x187,500','0%','RG','374705999','9:53:22','YP','YPTR1006','224666','415','1500.00','0%' UNION ALL

    SELECT '1521','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','100000','41500000','Regular Board','-','153844942','148436','Rp415.00','x186,000','0%','RG','374707112','9:54:09','KI','KITR1003','715904','415','100000.00','0%' UNION ALL

    SELECT '1522','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','2000','830000','Regular Board','-','153844943','148436','Rp415.00','x86,000','0%','RG','374707138','9:54:10','NI','NITR1002','757942','415','2000.00','0%' UNION ALL

    SELECT '1523','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','500','207500','Regular Board','-','153844944','148436','Rp415.00','x84,000','0%','RG','374708022','9:54:50','PD','PDTR1006','130494','415','500.00','0%' UNION ALL

    SELECT '1524','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','83500','34652500','Regular Board','-','153844962','148436','Rp415.00','x83,500','0%','RG','374709516','9:55:57','KI','KITR1004','600049','415','100000.00','0%' UNION ALL

    SELECT '877','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153828514','587218','Rp425.00','x51,500','0%','RG','374671739','9:33:46','PD','PDTR1007','728545','425','5000.00','0%' UNION ALL

    SELECT '878','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','2500','1062500','Regular Board','-','153828590','587218','Rp425.00','x46,500','0%','RG','374671933','9:33:51','PD','PDTR1005','122460','425','2500.00','0%' UNION ALL

    SELECT '879','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','500','212500','Regular Board','-','153828602','587218','Rp425.00','x44,000','0%','RG','374671966','9:33:52','NI','NITR1001','120149','425','500.00','0%' UNION ALL

    SELECT '880','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','2500','1062500','Regular Board','-','153828621','587218','Rp425.00','x43,500','0%','RG','374672018','9:33:54','KK','KKTR1005','339786','425','2500.00','0%' UNION ALL

    SELECT '881','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153828625','587218','Rp425.00','x41,000','0%','RG','374672034','9:33:54','YP','YPTR1006','659802','425','5000.00','0%' UNION ALL

    SELECT '882','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','12500','5312500','Regular Board','-','153828641','587218','Rp425.00','x36,000','0%','RG','374672098','9:33:56','AI','AITR1001','293662','425','12500.00','0%' UNION ALL

    SELECT '883','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153828663','587218','Rp425.00','x23,500','0%','RG','374672178','9:33:59','KK','KKTR1001','448650','425','5000.00','0%' UNION ALL

    SELECT '885','TRADE','Trd','','Jan 1 1900 9:34AM','Apr 12 2012 12:00AM','425','10000','4250000','Regular Board','-','153828685','587218','Rp425.00','x18,500','0%','RG','374672259','9:34:01','BS','BSTR1001','119875','425','10000.00','0%' UNION ALL

    SELECT '887','TRADE','Trd','','Jan 1 1900 9:34AM','Apr 12 2012 12:00AM','425','8500','3612500','Regular Board','-','153828737','587218','Rp425.00','x8,500','0%','RG','374672478','9:34:07','YP','YPTR1006','339523','425','126500.00','0%' UNION ALL

    SELECT '872','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','51500','21887500','Regular Board','-','153828451','587218','Rp425.00','x116,500','0%','RG','374671556','9:33:41','AI','AITR1001','237783','425','51500.00','0%' UNION ALL

    SELECT '873','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','11000','4675000','Regular Board','-','153828463','587218','Rp425.00','x65,000','0%','RG','374671619','9:33:42','PD','PDTR1001','407483','425','11000.00','0%' UNION ALL

    SELECT '874','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','1000','425000','Regular Board','-','153828473','587218','Rp425.00','x54,000','0%','RG','374671646','9:33:43','YP','YPTR1004','134763','425','1000.00','0%' UNION ALL

    SELECT '875','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','1500','637500','Regular Board','-','153828480','587218','Rp425.00','x53,000','0%','RG','374671665','9:33:44','PD','PDTR1004','591222','425','1500.00','0%' UNION ALL

    SELECT '818','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','500','212500','Regular Board','-','153827895','326552','Rp425.00','x500','0%','RG','374670078','9:33:00','KK','KKTR1003','555723','425','394000.00','0%' UNION ALL

    SELECT '545','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 12 2012 12:00AM','440','1709000','751960000','Regular Board','-','153826141','326552','Rp435.00','x1,709,000','0%','RG','374659503','9:30:12','YU','YUTR1001','290473','440','2289500.00','0%' UNION ALL

    SELECT '1918','TRADE','Trd','','Jan 1 1900 10:16AM','Apr 12 2012 12:00AM','425','180500','76712500','Regular Board','-','153858447','326552','Rp425.00','x180,500','0%','RG','374734244','10:16:55','KK','KKTR1004','466377','425','924000.00','0%' UNION ALL

    SELECT '1919','TRADE','Trd','','Jan 1 1900 10:16AM','Apr 12 2012 12:00AM','425','45000','19125000','Regular Board','-','153858448','326552','Rp425.00','x45,000','0%','RG','374734244','10:16:55','KK','KKTR1004','466377','425','743500.00','0%' UNION ALL

    SELECT '1572','TRADE','Trd','','Jan 1 1900 9:57AM','Apr 12 2012 12:00AM','410','1000','410000','Regular Board','-','153846025','326552','Rp410.00','x1,000','0%','RG','374626151','9:30:01','GA','GATR1001','169280','410','1093500.00','0%' UNION ALL

    SELECT '986','TRADE','Trd','','Jan 1 1900 9:34AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153829271','326552','Rp425.00','x5,000','0%','RG','374673850','9:34:50','YU','YUTR1001','290473','425','1903000.00','0%' UNION ALL

    SELECT '1364','TRADE','Trd','','Jan 1 1900 9:48AM','Apr 12 2012 12:00AM','425','14000','5950000','Regular Board','-','153839838','326552','Rp425.00','x14,000','0%','RG','374684670','9:39:52','XA','XATR1002','739184','425','155000.00','0%' UNION ALL

    SELECT '2221','TRADE','Trd','','Jan 1 1900 10:23AM','Apr 12 2012 12:00AM','430','10000','4300000','Regular Board','-','153861300','326552','Rp430.00','x27,500','0%','RG','374739819','10:23:08','YP','YPTR1008','201922','435','10000.00','0%' UNION ALL

    SELECT '2222','TRADE','Trd','','Jan 1 1900 10:23AM','Apr 12 2012 12:00AM','430','2000','860000','Regular Board','-','153861391','326552','Rp430.00','x17,500','0%','RG','374739933','10:23:11','PD','PDTR1001','407483','430','2000.00','0%' UNION ALL

    SELECT '2223','TRADE','Trd','','Jan 1 1900 10:23AM','Apr 12 2012 12:00AM','430','15500','6665000','Regular Board','-','153861406','326552','Rp430.00','x15,500','0%','RG','374739968','10:23:14','KK','KKTR1004','466377','430','250000.00','0%' UNION ALL

    SELECT '1661','TRADE','Trd','','Jan 1 1900 9:58AM','Apr 12 2012 12:00AM','410','3000','1230000','Regular Board','-','153847391','326552','Rp410.00','x3,000','0%','RG','374626151','9:30:01','GA','GATR1001','169280','410','741000.00','0%' UNION ALL

    SELECT '1700','TRADE','Trd','','Jan 1 1900 10:00AM','Apr 12 2012 12:00AM','415','2500','1037500','Regular Board','-','153848934','100173','Rp415.00','x223,000','0%','RG','374716222','10:00:42','KI','KITR1003','742276','415','2500.00','0%' UNION ALL

    SELECT '1701','TRADE','Trd','','Jan 1 1900 10:00AM','Apr 12 2012 12:00AM','415','2500','1037500','Regular Board','-','153848935','100173','Rp415.00','x220,500','0%','RG','374716223','10:00:42','KI','KITR1003','742276','415','2500.00','0%' UNION ALL

    SELECT '1176','TRADE','Trd','','Jan 1 1900 9:37AM','Apr 12 2012 12:00AM','435','100000','43500000','Regular Board','-','153831189','136310','Rp435.00','x118,000','0%','RG','374679287','9:37:33','AI','AITR1001','237783','435','100000.00','0%' UNION ALL

    SELECT '870','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','43500','18487500','Regular Board','-','153828449','164421','Rp425.00','x43,500','0%','RG','374671556','9:33:41','AI','AITR1001','237783','425','100000.00','0%' UNION ALL

    SELECT '871','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153828450','189306','Rp425.00','x5,000','0%','RG','374671556','9:33:41','AI','AITR1001','237783','425','56500.00','0%' UNION ALL

    SELECT '872','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','51500','21887500','Regular Board','-','153828451','587218','Rp425.00','x116,500','0%','RG','374671556','9:33:41','AI','AITR1001','237783','425','51500.00','0%' UNION ALL

    SELECT '89','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','125000','35625000','Regular Board','-','153573994','109051','Rp285.00','x125,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3203500.00','0%' UNION ALL

    SELECT '90','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','6000','1710000','Regular Board','-','153574008','609238','Rp285.00','x6,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3078500.00','0%' UNION ALL

    SELECT '91','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','6000','1710000','Regular Board','-','153574013','758837','Rp285.00','x6,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3072500.00','0%' UNION ALL

    SELECT '92','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153574028','142813','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3066500.00','0%' UNION ALL

    SELECT '93','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','34500','9832500','Regular Board','-','153574045','163807','Rp285.00','x34,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3056500.00','0%' UNION ALL

    SELECT '94','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','25000','7125000','Regular Board','-','153574055','164528','Rp285.00','x25,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3022000.00','0%' UNION ALL

    SELECT '95','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','25000','7125000','Regular Board','-','153574062','166400','Rp285.00','x25,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2997000.00','0%' UNION ALL

    SELECT '96','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','4500','1282500','Regular Board','-','153574075','219315','Rp285.00','x4,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2972000.00','0%' UNION ALL

    SELECT '97','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','2500','712500','Regular Board','-','153574088','282040','Rp285.00','x2,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2967500.00','0%' UNION ALL

    SELECT '98','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','87500','24937500','Regular Board','-','153574097','214569','Rp285.00','x87,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2965000.00','0%' UNION ALL

    SELECT '99','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153574119','282040','Rp285.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2877500.00','0%' UNION ALL

    SELECT '166','TRADE','Trd','','Jan 1 1900 9:59AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153590565','715581','Rp280.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1463000.00','0%' UNION ALL

    SELECT '167','TRADE','Trd','','Jan 1 1900 10:01AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153592028','164267','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1461000.00','0%' UNION ALL

    SELECT '168','TRADE','Trd','','Jan 1 1900 10:05AM','Apr 10 2012 12:00AM','285','100000','28500000','Regular Board','-','153593702','204216','Rp285.00','x100,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1451000.00','0%' UNION ALL

    SELECT '169','TRADE','Trd','','Jan 1 1900 10:06AM','Apr 10 2012 12:00AM','285','30000','8550000','Regular Board','-','153593933','221829','Rp285.00','x30,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1351000.00','0%' UNION ALL

    SELECT '170','TRADE','Trd','','Jan 1 1900 10:06AM','Apr 10 2012 12:00AM','285','68500','19522500','Regular Board','-','153594268','199581','Rp285.00','x68,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1321000.00','0%' UNION ALL

    SELECT '171','TRADE','Trd','','Jan 1 1900 10:07AM','Apr 10 2012 12:00AM','285','3000','855000','Regular Board','-','153594679','221258','Rp285.00','x3,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1252500.00','0%' UNION ALL

    SELECT '172','TRADE','Trd','','Jan 1 1900 10:09AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153595819','164284','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1249500.00','0%' UNION ALL

    SELECT '173','TRADE','Trd','','Jan 1 1900 10:14AM','Apr 10 2012 12:00AM','285','5000','1425000','Regular Board','-','153598117','100424','Rp285.00','x5,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1239500.00','0%' UNION ALL

    SELECT '174','TRADE','Trd','','Jan 1 1900 10:14AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153598130','221808','Rp285.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1234500.00','0%' UNION ALL

    SELECT '175','TRADE','Trd','','Jan 1 1900 10:18AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153599240','660363','Rp285.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1232500.00','0%' UNION ALL

    SELECT '176','TRADE','Trd','','Jan 1 1900 10:23AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153600837','450121','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1230500.00','0%' UNION ALL

    SELECT '23','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','58500','16672500','Regular Board','-','153573413','103949','Rp280.00','x58,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','5000000.00','0%' UNION ALL

    SELECT '24','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','100000','28500000','Regular Board','-','153573423','164532','Rp285.00','x100,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4941500.00','0%' UNION ALL

    SELECT '25','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','50000','14250000','Regular Board','-','153573426','164508','Rp285.00','x50,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4841500.00','0%' UNION ALL

    SELECT '26','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','25000','7125000','Regular Board','-','153573430','467574','Rp285.00','x25,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4791500.00','0%' UNION ALL

    SELECT '27','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153573431','164472','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4766500.00','0%' UNION ALL

    SELECT '28','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153573436','164534','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4756500.00','0%' UNION ALL

    SELECT '29','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','6000','1710000','Regular Board','-','153573438','340370','Rp250.00','x6,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4746500.00','0%' UNION ALL

    SELECT '30','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','22500','6412500','Regular Board','-','153573439','263891','Rp255.00','x22,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4740500.00','0%' UNION ALL

    SELECT '31','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','1500','427500','Regular Board','-','153573443','467538','Rp270.00','x1,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4718000.00','0%' UNION ALL

    SELECT '32','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','50000','14250000','Regular Board','-','153573446','164508','Rp285.00','x50,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4716500.00','0%' UNION ALL

    SELECT '33','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153573448','114241','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4666500.00','0%' UNION ALL

    SELECT '100','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','8500','2422500','Regular Board','-','153574138','360110','Rp285.00','x8,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2875500.00','0%' UNION ALL

    SELECT '101','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','14000','3990000','Regular Board','-','153574164','231188','Rp285.00','x14,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2867000.00','0%' UNION ALL

    SELECT '102','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','5500','1567500','Regular Board','-','153574165','106588','Rp285.00','x5,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2853000.00','0%' UNION ALL

    SELECT '103','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','5000','1425000','Regular Board','-','153574177','111691','Rp285.00','x5,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2847500.00','0%' UNION ALL

    SELECT '104','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153574266','146366','Rp285.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2842500.00','0%' UNION ALL

    SELECT '105','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','37000','10545000','Regular Board','-','153574267','164729','Rp285.00','x37,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2840500.00','0%' UNION ALL

    SELECT '106','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','5000','1425000','Regular Board','-','153574294','216005','Rp285.00','x5,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2803500.00','0%' UNION ALL

    SELECT '107','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','5000','1425000','Regular Board','-','153574318','115461','Rp285.00','x5,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2798500.00','0%' UNION ALL

    SELECT '108','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','1000','285000','Regular Board','-','153574319','210692','Rp285.00','x1,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2793500.00','0%' UNION ALL

    SELECT '109','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','84500','24082500','Regular Board','-','153574332','262603','Rp285.00','x84,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2792500.00','0%' UNION ALL

    SELECT '110','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','3500','997500','Regular Board','-','153574344','216058','Rp285.00','x3,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2708000.00','0%' UNION ALL

    SELECT '177','TRADE','Trd','','Jan 1 1900 10:30AM','Apr 10 2012 12:00AM','285','2500','712500','Regular Board','-','153602959','101354','Rp285.00','x2,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1220500.00','0%' UNION ALL

    SELECT '178','TRADE','Trd','','Jan 1 1900 10:41AM','Apr 10 2012 12:00AM','285','3500','997500','Regular Board','-','153606644','731066','Rp285.00','x3,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1218000.00','0%'

    Relation between both table is Substring(#test_table1.ID,8,6) equal AskClient or BidClient. and there's can be same ID on #test_table1, because it refer to same person but different accountNumber.

    From both table i has to get all of data from #test_table2 and FirstName,MiddleName,LastName from #test_table1.

    I has made this query

    SELECT

    test2.*,

    sellerFirstName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),

    sellerMiddleName = (SELECT TOP 1 test1.MiddleName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),

    sellerLastName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),

    buyerFirstName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient),

    buyerMiddleName = (SELECT TOP 1 test1.MiddleName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient),

    buyerLastName = (SELECT TOP 1 test1.LastName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient)

    FROM #test_table2 test2

    I realize that from my query i will get poor performance when it execute, but i have no idea how to relate between both table without subquery. I have create with join but it gives me more data than i need it. I need query that give better performance than mine.

    For information, i'm using SQL Server 2008 on Windows Server 2008

    Would anyone here help me, a suggestion or advice maybe. I'm sorry for my bad english. I really appreciate all of your help.

    Regards

  • August 8, 2012 at 8:49 am

    #1523156

    First, thank you for the setup, it will help a lot. Just one thing missing that would help more, expected results. What should the query return based on the sample data you provided. Please don't describe, show us in a third table like you provided us the sample data. This will give us something to actually test against.

    Plus, for me, it helps to see what I am trying to accomplish.

  • August 8, 2012 at 8:56 am

    #1523163

    i'm sorry for not describe it clearly. What i want is exactly same like query that i made before that i made before

    SELECT

    test2.*,

    sellerFirstName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),

    sellerMiddleName = (SELECT TOP 1 test1.MiddleName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),

    sellerLastName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),

    buyerFirstName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient),

    buyerMiddleName = (SELECT TOP 1 test1.MiddleName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient),

    buyerLastName = (SELECT TOP 1 test1.LastName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient)

    FROM #test_table2 test2

    When you run this query with table and data that i provide, that's exactly the return i expected. The problem is, my query has a poor performance, when the data get bigger. When i execute it with 400.000 record on #test_table1 and 100.000 record on #test_table2 it tooks 20 minutes and still not finish. So i need suggestion about how to make it more efficient.

    Thanks.

  • August 8, 2012 at 9:13 am

    #1523183

    I believe there's a problem with your sample data because there's not to much matches (only 19)

    However, you might find useful to use a CTE with the ranking function ROW_NUMBER.

    Here's an example:

    ;WITH Test AS(

    SELECTSUBSTRING(t.ID,8,6) AS id,

    t.FirstName,

    t.MiddleName,

    t.LastName,

    ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.ID) AS RN

    FROM#test_table1 t)

    SELECT

    test2.*,

    sellerFirstName = t1a.FirstName,

    sellerMiddleName = t1a.MiddleName,

    sellerLastName = t1a.LastName,

    buyerFirstName = t1b.FirstName,

    buyerMiddleName = t1b.MiddleName,

    buyerLastName = t1b.LastName

    FROM #test_table2 test2

    LEFT

    JOIN Test t1a ON t1a.ID = test2.AskClient AND t1a.RN = 1

    LEFT

    JOIN Test t1b ON t1b.ID = test2.BidClient AND t1a.RN = 1

    WHERE t1a.FirstName IS NOT NULL

    OR t1b.FirstName IS NOT NULL

    Last recommendations:

    - Don't just copy and paste, undestand it before you use it and test it

    - Don't use SELECT * in production, always use explicit columns.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 8, 2012 at 8:00 pm

    #1523532

    Luis Cazares (8/8/2012)

    I believe there's a problem with your sample data because there's not to much matches (only 19)

    However, you might find useful to use a CTE with the ranking function ROW_NUMBER.

    Here's an example:

    ;WITH Test AS(

    SELECTSUBSTRING(t.ID,8,6) AS id,

    t.FirstName,

    t.MiddleName,

    t.LastName,

    ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.ID) AS RN

    FROM#test_table1 t)

    SELECT

    test2.*,

    sellerFirstName = t1a.FirstName,

    sellerMiddleName = t1a.MiddleName,

    sellerLastName = t1a.LastName,

    buyerFirstName = t1b.FirstName,

    buyerMiddleName = t1b.MiddleName,

    buyerLastName = t1b.LastName

    FROM #test_table2 test2

    LEFT

    JOIN Test t1a ON t1a.ID = test2.AskClient AND t1a.RN = 1

    LEFT

    JOIN Test t1b ON t1b.ID = test2.BidClient AND t1a.RN = 1

    WHERE t1a.FirstName IS NOT NULL

    OR t1b.FirstName IS NOT NULL

    Last recommendations:

    - Don't just copy and paste, undestand it before you use it and test it

    - Don't use SELECT * in production, always use explicit columns.

    That's a behaviour of my data, i just have to relate both table, and it didn't has to be match at all.

    But thanks for your suggestion, i have modified your suggested query and i think it solve my problem. Thanks a lot guys.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply