7 Replies - 7636 Views - Last Post: 17 December 2012 - 02:49 PM Rate Topic: -----

#1 JG7000  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 9
  • Joined: 13-December 12

Read Insert Update Delete Form Multiple Tables In A Single DB

Posted 15 December 2012 - 07:33 AM

Dear friends, I have a single database in SQL Server 2010, with 3 tables, first table contains a members personal info, Second table conations 10 years overview of the amount paid by the and the third table for another 10 years overview.


At the moment I can search and display and update and delete from table one.

What I want to do is that when I search or display or update or delete I can use the info from the Second & Third table also, so I have the overview information from all the tables shown up in my from.


Question is how to or in what way I write an SQL statement which gets or sets data from all three tables in a single click of button, and how to write a single or multiple statements, hope this question is clear enough.


Kind regards
JG

Is This A Good Question/Topic? 0
  • +

Replies To: Read Insert Update Delete Form Multiple Tables In A Single DB

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3709
  • View blog
  • Posts: 5,954
  • Joined: 08-June 10

Re: Read Insert Update Delete Form Multiple Tables In A Single DB

Posted 15 December 2012 - 08:26 AM

Why do you have two tables for you overviews? Sounds a bit odd to split the data like that.

There are a few ways in which you can query data from multiple tables at once:



Which you'd use depends on the exact situation, and how your tables are set up, but in general I'd recommend trying to use them in the order I listed them. Joins tend to be faster than Subqueries, and Unions tend to be less useful than the other two.

P.S.
I've moved this thread into the MSSQL forum. This isn't really a C# question.

This post has been edited by Atli: 15 December 2012 - 08:28 AM

Was This Post Helpful? 0
  • +
  • -

#3 JG7000  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 9
  • Joined: 13-December 12

Re: Read Insert Update Delete Form Multiple Tables In A Single DB

Posted 15 December 2012 - 09:12 AM

Thank you for your reply, well you have asked why I am using two tables, well its actually there are three tables, the first table contains values like ID, name, address phone number and comments, the rest two tables contain 10 columns each, in which table one [ 2002,…2012] and table two [2012,…2022] and the values of each year is the subscription amount, for example 300 dollars . Like column 2002 has value of 300.
In details when I open up form, I see a layout like Snr which is generated automatically, and grayed out, then I have a unique ID text box with a search button, from which lets say if I insert ID and click search the I get all the values in the textboxes with personal info of let’s say John Doe, like name address phone nr. And right in front I have these sets of 10 textboxes each representing years , so I can either find John Doe and see his details and overview about the years he / she has paid subscription or not or I add a new member.
If you have any other idea or suggestion which is easier or smarter I am all ears.


View PostAtli, on 15 December 2012 - 08:26 AM, said:

Why do you have two tables for you overviews? Sounds a bit odd to split the data like that.

There are a few ways in which you can query data from multiple tables at once:



Which you'd use depends on the exact situation, and how your tables are set up, but in general I'd recommend trying to use them in the order I listed them. Joins tend to be faster than Subqueries, and Unions tend to be less useful than the other two.

P.S.
I've moved this thread into the MSSQL forum. This isn't really a C# question.

Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3709
  • View blog
  • Posts: 5,954
  • Joined: 08-June 10

Re: Read Insert Update Delete Form Multiple Tables In A Single DB

Posted 15 December 2012 - 07:55 PM

OK, I see. That design is completely wrong for a relational database. That design is something you would expect of a spreadsheet application like Excel, which SQL servers are not.

If I am understanding you, you have set up tables that look something like this:
Table: User
+--------+------+----------+
| UserID | Name | Whatever |
+--------+------+----------+
|      1 | John | ...      |
|      2 | Anna | ...      |
|      3 | Sam  | ...      |
+--------+------+----------+

Table: Subscriptions_2010-2012
+--------+------+------+------+
| UserID | 2010 | 2011 | 2012 |
+--------+------+------+------+
|      1 |  300 | 1000 |   25 |
|      2 |  375 |  467 |  340 |
|      3 |  500 | 3604 | 4500 |
+--------+------+------+------+


Am I close?

The problem with this design is that it makes it unnecessarily complicated to query the data, seeing as the years are actually column names rather than values; and it makes the database very inflexible, as you'll have to keep altering the table and/or database structure to add new years. That is something you'll want to avoid at (almost) any cost.

Instead, you'll want to make the Subscriptions table look something more like this:
+--------+------+--------+
| UserID | Year | Amount |
+--------+------+--------+
|      1 | 2010 |    300 |
|      1 | 2011 |   1000 |
|      1 | 2012 |     25 |
|      2 | 2010 |    375 |
|      2 | 2011 |    467 |
|      2 | 2012 |    340 |
|      3 | 2010 |    500 |
|      3 | 2011 |   3604 |
|      3 | 2012 |   4500 |
+--------+------+--------+


Notice there that the year values are actually stored as values and can therefore be queries like values. It also means you don't have to keep adding tables or columns every year. You just add rows to this one table.

As an example of why this structure is better, consider this scenario. Say you want to fetch the total subscription amount for a given user, within a given time frame. For the first, spreadsheet like table, you'd have to do things like this:
SELECT [2010] + [2011] + [2012] AS total
FROM [Subscriptions_2010-2012]
WHERE [UserID] = 1;


In order for this to work, you have to manually generate the formula to sum the values for the years. So if you change the date range, you'll have to change the formula. Now consider if you wanted to combine values for, say, a thousand years. The query for that would become quite unmanageable.

Now lets look at the same concept for the second table:
SELECT SUM(Amount) AS total
FROM Subscriptions
WHERE UserID = 1
AND [Year] BETWEEN 2010 AND 2012


There I can use the SQL server's SUM function to add the values for me, and define the date range using an actual date range, with the BETWEEN ... AND operator. This is far more flexible, and a lot easier to use in your application code. I can change this query to fetch the total for a thousand years by simply changing the year numbers in that range.

And this is just one example. There are a set of rules we use when designing relational databases; guidelines that will help ensure the integrity of the data and the flexibility of the database itself. We call that Normalization. You should look into that if you are going to be working with relational databases.
Was This Post Helpful? 2
  • +
  • -

#5 JG7000  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 9
  • Joined: 13-December 12

Re: Read Insert Update Delete Form Multiple Tables In A Single DB

Posted 17 December 2012 - 11:05 AM

Now I have a windows form application, where I can insert and update (here I have a search button) and delete information about members. I have two tables in a single database, table one contains personal information, and a distinct ID, whereas table two has also has distinct ID and year and amount columns.
I can for example add John Doe and his personal information plus ten years and the amount he has paid for ten years in the second table, all is ok up to this step.
I have one form where all this operation is taking place. My question is in what way I can retrieve information about John Doe’s subscriptions overview, for example by selecting any year I want to display the amount he has paid?
I have a search button in front of distinct ID text box where I can retrieve information about John Doe, but only the last entries from the second table, I mean the personal info values can easily manipulated, and that part is as the way it should be, but as the question arrives how to select a single year about John Doe and view values for that particular year.
Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland  Icon User is online

  • (╯°□°)╯︵ (~ .o.)~
  • member icon


Reputation: 4405
  • View blog
  • Posts: 7,646
  • Joined: 08-June 10

Re: Read Insert Update Delete Form Multiple Tables In A Single DB

Posted 17 December 2012 - 12:29 PM

I think you need to break down the question a bit. You're asking very specific things about code and data we don't have to look at. I'm not sure what exactly you're asking because I don't have your database and code to see.
Was This Post Helpful? 0
  • +
  • -

#7 JG7000  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 9
  • Joined: 13-December 12

Re: Read Insert Update Delete Form Multiple Tables In A Single DB

Posted 17 December 2012 - 12:53 PM

How to retrieve an individual’s yearly paid subscriptions, like John Doe = 2002  300
Again I select John Doe or distinct ID = 2003 400,
Was This Post Helpful? 0
  • +
  • -

#8 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5417
  • View blog
  • Posts: 11,604
  • Joined: 02-June 10

Re: Read Insert Update Delete Form Multiple Tables In A Single DB

Posted 17 December 2012 - 02:49 PM

Wasn't this addressed in the other thread you opened for this same topic/question/subscription database?
http://www.dreaminco...1&#entry1768286
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1