Database

FirstPrevNext

select data from two tables ...??? vicky4572-Apr-07 9:44 vicky457 2-Apr-07 9:44  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! Re: select data from two tables ...??? Krish - KP2-Apr-07 17:40 Krish - KP 2-Apr-07 17:40  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 Re: select data from two tables ...??? vicky4573-Apr-07 5:02 vicky457 3-Apr-07 5:02  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. Re: select data from two tables ...??? jijoaresseriljose2-Apr-07 20:59 jijoaresseriljose 2-Apr-07 20:59  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 Re: select data from two tables ...??? vicky4573-Apr-07 4:57 vicky457 3-Apr-07 4:57  Hi, Thank you very much! It works! SQL 2005 Express Question Ian Perera2-Apr-07 8:31 Ian Perera 2-Apr-07 8:31  Hey everyone; I would like to know if sql 2005 express edition will allow me to create web services. what i want to do is write an application that will be able to communicate to a sql database via http. i also addtional help in creating the web service, i'm new to sql 2005 and need all the help i can get. thanks I@n Re: SQL 2005 Express Question Brent Lamborn3-Apr-07 5:35 Brent Lamborn 3-Apr-07 5:35  Yes I believe you can create a web service in SQL Server Express. I think for the msot part Express will most everything the full version will as far as web service - could be wrong though. Check out this article: http://davidhayden.com/blog/dave/archive/2006/03/31/2897.aspx[^] You might also check out the code in the Teach Starter Kit[^] for Express version. Looks like there might be some code of use in it. "Half this game is ninety percent mental." - Yogi Berra If you can read thank a teacher, if you can read in English, thank a Marine. DataGridView vb.net 2005 kindman_nb2-Apr-07 7:59 kindman_nb 2-Apr-07 7:59  Hi When I view a table withen DataGridView . for eg .. then rows count on the table is 5 rows .. you found at the end of rows on the dataagrid control a balck area with the back color ,,, Can I make the DatagridView Countinue view it's rows even it is empty .. I hope that my idea is clear jooooo error while creating the table pratima12-Apr-07 3:19 pratima1 2-Apr-07 3:19  Hi, i am getting the following error while i am trying to create a table in the database in sql2005. Unable to create table. The transaction ended in the trigger. The batch has been aborted. p.sethi p.sethi Re: error while creating the table gauthee2-Apr-07 3:23 gauthee 2-Apr-07 3:23  When exactly you are creating this table? In a trigger or something?? Gautham Re: error while creating the table pratima12-Apr-07 3:34 pratima1 2-Apr-07 3:34  i am not creating the table in trigger.i just enter into the database,tables,create new table.like this i am creating but while saving i am getting the error. Unable to create table. The transaction ended in the trigger. The batch has been aborted. p.sethi Re: error while creating the table gauthee2-Apr-07 3:47 gauthee 2-Apr-07 3:47  Couldnt exactly understand the cause for the problem!! Do the formal check i.e: Check whether you are giving the same tablename! Any errors in the design/data of your table before saving it! Gautham Re: error while creating the table Vimalsoft(Pty) Ltd11-Apr-07 0:58 Vimalsoft(Pty) Ltd 11-Apr-07 0:58  Post your Syntax here and infact write all the systax here then we will be able to help you Vuyiswa Deletion with relationship sujithkumarsl2-Apr-07 2:45 sujithkumarsl 2-Apr-07 2:45  I have three tables in msaccess which are related with each other using forign keys. I want to delete the row of the innertable and second table with a single query can u help me? My small attempt... Re: Deletion with relationship joon vh.2-Apr-07 3:15 joon vh. 2-Apr-07 3:15  You should look for information about Referential integrity. You can force relations to be updated cascading or not in the properties of a relation. Wiki on referential integrity: http://en.wikipedia.org/wiki/Referential_integrity[^] The syntax for a delete command is found here: http://www.w3schools.com/sql/sql_delete.asp[^] Visual Studio can't evaluate this, can you? public object moo<br /> {<br /> __get { return moo; }<br /> __set { moo = value; }<br /> } Hi sree reddy1-Apr-07 23:24 sree reddy 1-Apr-07 23:24  I need ur help for designing PAYROLL DataBase. Any Knows Plz Send Me(US PAYROLL)> sree reddy Re: Hi sree reddy1-Apr-07 23:26 sree reddy 1-Apr-07 23:26  I need ur help for designing PAYROLL DataBase. Any body Knows Plz Send Me(US PAYROLL) sree reddy Re: Hi Colin Angus Mackay2-Apr-07 0:08 Colin Angus Mackay 2-Apr-07 0:08  sree reddy wrote: I need ur help for designing PAYROLL DataBase Start with the customer's requirements. What do they require the payroll database to hold? What do they require it to do? Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website Re: Hi jijoaresseriljose2-Apr-07 1:31 jijoaresseriljose 2-Apr-07 1:31  hi, Hey...how others can db design upon your requirements ..... regards jijo jose New to SQL Zalro1-Apr-07 20:52 Zalro 1-Apr-07 20:52  Hi I am new to SQL .. Curently I am trying to do database mirroring in SQL Server Management Studio Express.. After i have made a back up and restore it on another computer, there would be a Mirror choice for me to click under the tasks option after right clicking on my database . But the Mirror choice does not appear there.. What could the problem be ? Anyone can help me ? Thanks in advance... SQl Query Problem Devkranth Kishore Vanja1-Apr-07 20:16 Devkranth Kishore Vanja 1-Apr-07 20:16  hi to all My name is Devkranth .. i need help in sql.... i have 3 tables to retrieve data and display it.. there is no Primary key in any table.. but there is a common column , so if i join the tables i am getting rows duplicated... plz help me.. thanks in advance Re: SQl Query Problem jijoaresseriljose1-Apr-07 20:24 jijoaresseriljose 1-Apr-07 20:24  hi, you can add identity column for these tables and join with this column. regards jijo jose Re: SQl Query Problem Devkranth Kishore Vanja1-Apr-07 20:41 Devkranth Kishore Vanja 1-Apr-07 20:41  hi jojo Thanks 4 ur reply.. i will just try it and come back to u.. Re: SQl Query Problem pradeep.shukla1-Apr-07 20:34 pradeep.shukla 1-Apr-07 20:34  after joining all tables use one of them and put a criteria like this --where fielname in ( select distinct fieldname from table1)-- this may solve the of duplicacy. Pradeep Shukla Re: SQl Query Problem Devkranth Kishore Vanja1-Apr-07 20:43 Devkranth Kishore Vanja 1-Apr-07 20:43  thanks Pradeeop well will try it and get back soon..

Last Visit: 31-Dec-99 18:00     Last Update: 19-Apr-24 7:12 Refresh ᐊ Prev1...1611161216131614161516161617161816191620 Next ᐅ

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.