How To Relate Two Fields Of The Same Table
Apr 18, 2007
Hi,
I am new to sql and is working with sql server managment 2005 +c# 2005.
My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.
there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.
In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.
I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)
How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.
Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.
Priyadarshini
View 1 Replies
ADVERTISEMENT
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Feb 16, 2013
I have two tables
Employer
Employee
How can I relate the two tables ,so that I know that who works for who.
View 4 Replies
View Related
May 2, 2008
Lets sat that i have 3 tables that i want to relate together manually (no wizards) by using SQL statements.I am using Microsoft SQL Server 2005 in Visual Web Developer 2008 using VB programming (ADO.NET)The 3 tables are as follows:
FloorRoomLocation
Fields inside each of the tables can be found below:
Floor Table(PK) ID(Required) NameDisplayOrder
Room Table(PK) ID(Required) Name(PK, FK) FloorIDExchangeIDExtensionIPDeviceNameRemarks(Required) Equipment(Required) Floor2IDFloorLocationXFloorLocationY
Location Table(PK,FK) FloorID (Required) CountryName(Required) CityName(Required) BuildingName
My Intended relationships between the 3 tables are as follows:
ID field in Floor table is referenced by the field named FloorID in the Room Table
ID field in Floor table is referenced by the field named FloorID in the Location table
NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table.
Floor Table has a 1 to Many Relationship with the Room Table.
I Have already done the physical relationships between each of the 3 tables in the Database Diagram.
All i want is the SQL statement that does the link/relationship between this 3 tables, that's all :)
View 6 Replies
View Related
May 5, 2008
Hi all,
Lets say that i have 3 tables, they are already related together, information on each of the 3 tables and their relationship between eachother can be found below:
I am using Microsoft SQL Server 2005 Database in Visual Web Developer 2008 using VB programming (ADO.NET)
The 3 tables are as follows:
Floor
Room
Location
Fields inside each of the tables can be found below:
Floor Table
(PK) ID
(Required) Name
DisplayOrder
Room Table
(PK) ID
(Required) Name
(PK, FK) FloorID
ExchangeID
Extension
IP
DeviceName
Remarks
(Required) Equipment
(Required) Floor2ID
FloorLocationX
FloorLocationY
Location Table
(PK,FK) FloorID
(Required) CountryName
(Required) CityName
(Required) BuildingName
The Relationships between the 3 tables are as follows:
ID field in Floor table is referenced by the field named FloorID in the Room Table
ID field in Floor table is referenced by the field named FloorID in the Location table
NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table.
Floor Table has a 1 to Many Relationship with the Room Table.
I Have already done the physical relationships between each of the 3 tables in the Database Diagram, in Visual Web Developer 2008 Express Edition under the Database Explorer
So i have created this form to accept user input:
this form is in a aspx page called AddRooms.aspx
Country Name:______________ (data sent to location table)
City Name:_________________ (data sent to location table)
Building Name:______________ (data sent to location table)
FloorID:[This is a DropDownList which is databinded to the Floor Table]
Room Name:_______________ (data sent to room table)
*Just imagine that the above is a form to accept user input
*With a hit of a submit button all data will be sent to their respective tables in the database
Now i know that i have to JOIN tables in my database in order for my application to work, but i have no experience performing JOIN, i need great guys like you to help me apply JOIN to the 3 tables mentioned above, do let me know if you have any doubts in what i am trying to do.
Do give me that code that i can either apply in the (VB environment Visual Web Developer 2008) and/or in Microsoft SQL Server 2005 environment
Just need the SQL Statment that i will need to construct to perform the JOIN i need to.
Please Help
Many Thanks : )
View 1 Replies
View Related
Feb 8, 2007
I have a contact table and a customer table. The two tables will contain columns like
First name
Last Name,
Date of Birth
Post Code,
House Number
Street Name
etc.
I would like to find the different combinations in which I can relate the customer and contact data.
Like its is possible that the first name and last name are same but date of birth is different. This indicates that the contact and customer is the same. Now I do not know these combinations and I would like to have this set generated for me.
From Integration Service (Sql Server 2005) I get the data and I would like to know the patterns in which data will differ. Is there any way of achieving this?
I am very new to Data Mining and would like to have some direction as to how to progress with this.
View 1 Replies
View Related
May 30, 2002
Hello Everyone,
Please stick with me for a second...
If someone dealt with tables related to US State/County and ZIP.
We found a company on the Web which have ALL US counties, states, zips and cities in one big table.
We need somehow to break this table( about 76K records) into multiple related tables and estabsish Some sort of relationship among these tables.
1.State table(design)
ID
State_Name
2.County table( design)
ID
County_Name
Relationship_to_State
For example if state = 'NY'; then all Counties falls in NY should have relationship '01'. For NJ '02' and so on.
3.Zipcode table( design-same as for county)
If someone has any idea how to solve this problem that would be really appreciated.
Thanks a lot in advance.
View 2 Replies
View Related
May 23, 2006
Iterating through Running packages as in this code from MSDN:
RunningPackages pkgs = app.GetRunningPackages("yourserver");
// Enumerate through each package in the collection and display some data.
foreach(RunningPackage package in pkgs)
{
Console.WriteLine("InstanceID: "+package.InstanceID);
Console.WriteLine("PackageDescription: "+package.PackageDescription);
Console.WriteLine("PackageID: "+package.PackageID);
Console.WriteLine("PackageName: "+package.PackageName);
Console.WriteLine("UserName: "+package.UserName);
}
How would I find any rows in sysdtslog90 related to that instance of RunningPackage (i.e. the 'package' variable)? The InstanceID does not seem to relate to the executionID that is in the log table.
Thanks.
View 15 Replies
View Related
Feb 12, 2007
So I have a question and I will give a related example. Say I have a list of records in a table:
Product Cost Active
hat 1.00 false
coat 2.00 true
I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer
Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update.
Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in its item list, but I also don't want all the items to be in the list.
View 6 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Jan 18, 2006
Hello,I'm using a shape query, but instead of using a simple clause "RELATEfield1 to field2" (relates the parent to the child), i wan't to use 2relates. somthing like "RELATE field1 to field2 AND field3 to field4".I want to receive in the children RS only the records who apply bothconditions.How do i do that ?Thanks !
View 2 Replies
View Related
Apr 23, 2008
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
View 7 Replies
View Related
Aug 31, 2006
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
View 1 Replies
View Related
May 29, 2008
I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it
I need that query for alter table
in it
reply me soon
View 14 Replies
View Related
Jun 13, 2008
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.
How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)
Thanks!
-Kurt
View 1 Replies
View Related
Feb 14, 2006
I want to let a user a fields and select the data type and size from a short list. For example: nvarchar(?), Money, Int, or Bit. These are most likely the only choices for now. There will be a web page that lets them enter a field name and select a data type.
Can someone suggest how the sproc might look? Here is what I have so far.
CREATE PROCEDURE [dbo].AddCustomField ASALTER TABLE MyDataSQL.dbo.tblCustomFields ADD @FieldName @FieldDataType NULLGO
Thank you,
View 1 Replies
View Related
Oct 23, 2007
I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.
Table Fields
Tbl_Date_Dimension [Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact] [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id
The new table is called Tbl_Sales_Growth
Here's the information on the new table:
Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
View 2 Replies
View Related
Jun 25, 2006
I am trying to access the fields in a table using Visual Basic in Web Developer .NET
View 1 Replies
View Related
Jan 13, 2007
I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName. Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name. So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email
View 3 Replies
View Related
Jan 4, 2004
My users login from the tbl_users table.
I would like to store all information on them in this table but that amounts to quite a lot of fields (about 50) and some of these will store a lot of HTML text in them.
Is it best to split the table up for performance reasons or will it make little difference?
Thanks,
JB
View 4 Replies
View Related
Aug 9, 2004
Hello all,
How can i write a sql statement that can give me the name of the fileds in a specified table.
Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.
any help.
View 25 Replies
View Related
Mar 6, 2000
I'm new to SQL Server 7. Is there a limit to the number of fields that a table can have?
View 1 Replies
View Related
Jan 13, 2007
I have maked this little code but dont know the way to insertt new table and fileds in database DATABASE01...
Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = "driver={SQL Server};server=DVD377-14D9E48CSQLEXPRESS;uid=;pwd=;database=master"
objConn.Open
objConn.Execute "CREATE DATABASE DATABASE01"
''''''''''''''''''
'"CREATE table Mytable"
''''''''''''''''''
''''''''''''''''''
'"CREATE filed1" in text format
'"CREATE filed2" in text format
'"CREATE filed3" in text format
'"CREATE filed4" in date format(DD/MM/YYYY)
''''''''''''''''''
objConn.Close
End Sub
View 1 Replies
View Related
Oct 17, 2007
I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?
View 14 Replies
View Related
Nov 29, 2014
I have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.
For example
NAME1 = JAY JOHN SMITH
NAME2 = JOHN SMITH JAY or SMITH JOHN JAY
Is there a way to somehow reorder these fields and then compare using SQL?
View 2 Replies
View Related
May 5, 2007
I'm kind of new to SQL and would appreciate some help with this. I have a table named Company. In this table there are fields called CompanyID, CompanyName, Address, and Year. (There are other fields too, but they are not relevant for this post.) There are multiple companies in the table and each company has a unique ID number. However, each company can have up to three records in the table. (One for the year 2005, one for the year 2006, and one for the year 2007.) Currently, only a company's 2007 record has data for the address. It's 2005 and 2006 records have addresses that are blank. I want to be able to update the 2005 and 2006 records with address from the 2007 record. Obviously, a very simple update won't work because there are multiple companies and multiple records that need updating. Can anyone fill me in on the SQL statement(s) I would need to make this happen? I would be extremely appreciative.
View 5 Replies
View Related
Jan 29, 2008
Hello to all,
i want to make a table in order to keep the data of a hard disk.
e.g. create table disk{
capacity integer,
name varchar(50),
ports integer,
buffer char(1));
For example i want this hard disk to have these fields and i would like from the user to type me only the capacity because it is going to be a required field and the other fields(name,ports,buffer) to be optional!
How do i declare optional fields in a create table command?
and then how do i declare/define these fields in insert too?
Thanks, in advance!
View 3 Replies
View Related
Jul 23, 2005
Hello,I would like to create a stored procedure that would compare the fieldsof two tables and their types. If they are different the user iswarned.How can I do that ?thx
View 3 Replies
View Related
Feb 2, 2007
I have a table that display large amounts of text in some fields. The table only has two columns (Field Name and Field Data). If the Field data is too large to fit on the same page as the field above it, it pushes to the next page, and then starts printing at the top of that page (the next page down).
I tried setting the "Keep Together" property of the table = True, but this was of no use.
Has anyone found a way to work around this, and if so could you let me know what you had to do. It may just be a SQL Server default setting that cannot be changed. I just want to research all possibilities before reporting back to the users.
Thank you,
T.J.
View 6 Replies
View Related
Aug 16, 2006
Hi,
I have 240 sensors to sample one time per second,then store the data in sql server.
Is it reasonable that the table has 240 fields?If do,it will generate about 70,000 records every day.One month records will be 2,100,000.
If I divide the 240 sampling data into 10 records,every record has 24 fields.Then every day 700,000 records will generate.One month the record number is 21,000,000.It is terrible.
Which method is better,or who can give me another better method or some suggestions.
I need your help.Thank a lot.
View 9 Replies
View Related
Mar 19, 2007
I am working on a project which requires having a form that must be filled by the user. The number of the fields on this form is arround 150. Is it a good idea to have a database table with 150 fields (columns)? If its not what would be a better approach for this case? Handling this many fields on a windows form is another issue but first I would like to know about how to deal with the data storage.
Any help is appreciated.
Thanks
View 9 Replies
View Related
Mar 6, 2008
Ha all again.
I have this issue:
I have 2 tables:
t1 (id,field1,field2,field3)
and t2 (id,field_name,field_value)
is possible with trigger to insert only changed fields on t1 to t2 like
(changing field1 on t1 with id 21 cause a new row in t2 with fields id=21,field_name="field1", field_value=field1.value)
i hope you understand what i look for.
Is this trigger solution ?
CREATE TRIGGER MAP_CHANGESON t1AFTER INSERT,UPDATEBEGIN
DECLARE @ID SOMEDATA;DECLARE @FIELD1 SOMEDATA;DECLARE @FIELD2 SOMEDATA;DECLARE @FIELD3 SOMEDATA;SET @ID=(SELECT ID FROM INSERTED)SET @FIELD1=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD1<>T2.FIELD1)SET @FIELD2=(SELECT FIELD2 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD2<>T2.FIELD2)SET @FIELD3=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD3<>T2.FIELD3)IF @FIELD1<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD1')IF @FIELD2<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD2','@FIELD2')IF @FIELD3<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD3')RETURNEND
View 7 Replies
View Related
Feb 9, 2007
Hi
I'm basically new using reporting services, I hope someone can help me
I'm using two datasets in the same table this is because I that was the only way to obtain the data that I need, my problem is that when I try to use the folowing statment =Fields!Total.Value/Fields!Total2.Value,"seconddataset" I got an error because the scope, the expression edtior give me the Sum(Fields!Total2.Value,"seconddataset") wich it works but that is not what I want, I need to divide by the single value not the total Sum
the table is using the firsdataset but I need to use some fields from the seconddataset
I hope someone can help me
thanks in advance
View 3 Replies
View Related
