select data from two tables ...???
-
Hi, I have two tables as following: Table1: AccountID Symbol Fee Price 1 AAA 10 150 1 AAA 20 250 1 BBB 30 350 2 AAA 40 450 2 CCC 50 550 3 DDD 60 650 4 EEE 70 750 6 HHH 15 250 Table2: AccountID Symbol Share Dollar 1 AAA 100 1100 1 BBB 200 2200 2 AAA 300 3300 2 AAA 400 4400 2 CCC 500 5500 3 DDD 600 6600 4 FFF 700 7700 5 GGG 800 8800 These two tables have two common fields: AccountId and Symbol. What I want to do is to get all the data from both tables based on the same accountid and the same symbol. If one table (A) doesn't contain the account or symbol in the other table (B), just fill the column in table (A) with blanks. The result should look like: AccountID Symbol Fee Price Share Dollar 1 AAA 10 150 100 1100 1 AAA 20 250 --- --- 1 BBB 30 350 200 2200 2 AAA 40 450 300 3300 2 AAA -- --- 400 4400 2 CCC 50 550 500 5500 3 DDD 60 650 600 6600 4 EEE 70 750 --- ---- 4 FFF -- --- 700 7700 5 GGG -- --- 800 8800 6 HHH 15 250 --- ---- The columns Fee and Price in Table1 don't have relationship with the columns Share and Dollar in Table2. All I need is to display data from both table based on Account and Symbol. And I need to do it using T-SQL. Any hint and suggestion would be appreciated. Thanks in advance!
-
Hi, I have two tables as following: Table1: AccountID Symbol Fee Price 1 AAA 10 150 1 AAA 20 250 1 BBB 30 350 2 AAA 40 450 2 CCC 50 550 3 DDD 60 650 4 EEE 70 750 6 HHH 15 250 Table2: AccountID Symbol Share Dollar 1 AAA 100 1100 1 BBB 200 2200 2 AAA 300 3300 2 AAA 400 4400 2 CCC 500 5500 3 DDD 600 6600 4 FFF 700 7700 5 GGG 800 8800 These two tables have two common fields: AccountId and Symbol. What I want to do is to get all the data from both tables based on the same accountid and the same symbol. If one table (A) doesn't contain the account or symbol in the other table (B), just fill the column in table (A) with blanks. The result should look like: AccountID Symbol Fee Price Share Dollar 1 AAA 10 150 100 1100 1 AAA 20 250 --- --- 1 BBB 30 350 200 2200 2 AAA 40 450 300 3300 2 AAA -- --- 400 4400 2 CCC 50 550 500 5500 3 DDD 60 650 600 6600 4 EEE 70 750 --- ---- 4 FFF -- --- 700 7700 5 GGG -- --- 800 8800 6 HHH 15 250 --- ---- The columns Fee and Price in Table1 don't have relationship with the columns Share and Dollar in Table2. All I need is to display data from both table based on Account and Symbol. And I need to do it using T-SQL. Any hint and suggestion would be appreciated. Thanks in advance!
-
Hi, I have two tables as following: Table1: AccountID Symbol Fee Price 1 AAA 10 150 1 AAA 20 250 1 BBB 30 350 2 AAA 40 450 2 CCC 50 550 3 DDD 60 650 4 EEE 70 750 6 HHH 15 250 Table2: AccountID Symbol Share Dollar 1 AAA 100 1100 1 BBB 200 2200 2 AAA 300 3300 2 AAA 400 4400 2 CCC 500 5500 3 DDD 600 6600 4 FFF 700 7700 5 GGG 800 8800 These two tables have two common fields: AccountId and Symbol. What I want to do is to get all the data from both tables based on the same accountid and the same symbol. If one table (A) doesn't contain the account or symbol in the other table (B), just fill the column in table (A) with blanks. The result should look like: AccountID Symbol Fee Price Share Dollar 1 AAA 10 150 100 1100 1 AAA 20 250 --- --- 1 BBB 30 350 200 2200 2 AAA 40 450 300 3300 2 AAA -- --- 400 4400 2 CCC 50 550 500 5500 3 DDD 60 650 600 6600 4 EEE 70 750 --- ---- 4 FFF -- --- 700 7700 5 GGG -- --- 800 8800 6 HHH 15 250 --- ---- The columns Fee and Price in Table1 don't have relationship with the columns Share and Dollar in Table2. All I need is to display data from both table based on Account and Symbol. And I need to do it using T-SQL. Any hint and suggestion would be appreciated. Thanks in advance!
hi, This is the complete query to get your output......... SELECT accountid=case when isnull(a.AccountID,'')='' then b.accountid else a.accountid end, symbol=case when isnull(a.Symbol,'')='' then b.Symbol else a.Symbol end , a.Fee, a.Price, b.Share, b.Dollar FROM tableA a FULL outer JOIN tableB b on a.AccountID = b.AccountID AND a.Symbol = b.Symbol order by accountid,symbol regards
jijo jose
-
hi, This is the complete query to get your output......... SELECT accountid=case when isnull(a.AccountID,'')='' then b.accountid else a.accountid end, symbol=case when isnull(a.Symbol,'')='' then b.Symbol else a.Symbol end , a.Fee, a.Price, b.Share, b.Dollar FROM tableA a FULL outer JOIN tableB b on a.AccountID = b.AccountID AND a.Symbol = b.Symbol order by accountid,symbol regards
jijo jose
-
SELECT a.AccountID, a.Symbol, a.Fee, a.Price, b.AccountID, b.Symbol, b.Share, b.Dollar FROM tableA a FULL OUTER JOIN tableB b IB a.AccountID = b.AccountID AND a.Symbol = b.Symbol
Regards KP
Hi, Thank you for your help! I have gotten the answer from other site: SELECT IsNull(T1.AccountID, T2.AccountID) AS AccountID, ISNull(T1.Symbol, T2.Symbol) AS Symbol, T1.Fee, T1.Price, T2.Share, T2.Dollar FROM Table1 AS T1 FULL OUTER JOIN Table2 AS T2 ON T1.AccountID = T2.AccountID AND T1.Symbol = T2.Symbol ORDER BY AccountID, Symbol It's basically the same as the post from jose.