Store As VARCHAR Or Normalise In A Child Table?
Jun 1, 2007
Should I change my current design which has a Lookup Child Table and a Parent Table containing a ID link tot he Child Table, and instead just putting a varchar for the Value in the Parent Table?
The issue is that the value is unique for a large percentage of cases (although some specific values reoccur very frequently).
However, the parent table has a high number of inserts, and that in turn often requires inserts to the Child Table, which in turn pushes the Child Table out of shape, and performance suffers.
The data is very seldom used (usually for debugging only)
Should I de-Normalise this for performance?
Details of the actual circumstances:
I have a table that Logs each "page hit" on our web site. That stores the "Referrer".
We store the Referrer for the SESSION separately (the first page viewed will have an external referrer, thereafter all pages will have an "internal" referrer).
This Referrer data is used VERY LITTLE - just for the occasional debugging issue.
Within some URLs are frequently used (www.mydomain.com/HOME.ASP) and others contain &Parameters and stuff and are either relatively unique(www.mydomain.com/ViewBasket.ASP&BasketID=1234) or variable (www.mydomain.com/ViewProduct.ASP&ProductID=1234)
Currently I store the Referrer in a Child "Lookup" Table, and the PK ID in the "Logging" Table.
Some analysis of the data shows:
119,509 rows (pages logged [purged after a couple of days, only includes data where the Referrer is not blank!])
Of those there are 10,760 distinct Referrer values. An average of 11 Page Hits per Referrer value.
On the face of it that suggests that using a child table is good! However, for each row inserted into the Page Hit table I have to do the following to get the Referrer ID:
SELECT @MyReferrerID = ID FROM MyLookupTable WHERE MyValue = @MyReferrerValue
IF @@ROWCOUNT = 0
INSERT ... @MyReferrerValue ...
SELECT @MyReferrerID = @@IDENTITY
(MyLookupTable has a PK on [ID] and a Unique Index on [MyValue] )
(In a busy month we will add 100,000 rows to this Lookup table, in a slack month 20,000. These get "purged" after a while, so the table size stays about the same. The table is defragged [if required] daily and the Stats updated. There are 350,000 rows in MyLookupTable)
Obviously some values in MyLookupTable reoccur, others are unique - and get purged after a while. Inserting these new entries pushes MyLookupTable out of shape, the SHOW_STATISTICS start to look awful, an sp_recompile sorts it out (Why? The query plan doesn't change ... although obviously the "shape" of the table has, maybe the Stats got auto-updated, would that change anything?)
So I'm thinking about just storing @MyReferrer as a VARCHAR in the Logging table, and throwing away MyLookupTable.
So really I'm just looking at performance, not Normalisation ...
What do you think?
Kristen
View 2 Replies
ADVERTISEMENT
Nov 1, 2007
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View 7 Replies
View Related
Mar 30, 2006
Hi all.
I have a SQL Server table which I need to normalise. To simply what I'm trying to achieve, here's an example table...
Create Table Sample( Id Integer Identity(1,1) Not Null, Data VarChar(6000) )
Insert Into Sample( Data ) Values( 'This is the first line
This is the second line' )
What I'm trying to achieve is to pass the entire table. Then split each line of the Data column into it's own row.
Does anyone have any suggestions as to how I can accomplish this using SSIS?
View 1 Replies
View Related
Jun 18, 2007
If you serialize the image into a string ? If you want to save a byte array to a string, do you have to use a base64 encoded string ?
View 1 Replies
View Related
Aug 25, 2004
I am using the varchar data type in sql to store a comma-delimited string of multiple selections from a checkboxlist.
The string only has about 28-30 characters in it, but it maxes out the sql row length and I get the 8060 error message every time.
Here is some of the code:
"Dim industry1list As String
Dim li As ListItem
industry1list = ""
For Each li In industry1.Items
If li.Selected = True Then
industry1list = industry1list & li.Value & ","
End If
Next
....
MyCommand.Parameters.Add(New SqlParameter("@industry1", SqlDbType.VarChar, 60))
MyCommand.Parameters("@industry1").Value = (industry1list)"
I would appreciate any coaching you have for me to get back on track. Thanks, Bob
View 6 Replies
View Related
Mar 21, 2008
My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()
View 8 Replies
View Related
Feb 8, 2007
Hi,
My package dumps the errors into a table. The problem is, it couldnt dump Error Output column to a varchar field. I have added an script component in between to transform to string but no success.
I tried ErrorOutput.GetBlobData(0, ErrorOutput.Length)
but when I query the database, it says "System.Byte[]'
I will appreciate the responses to this post.
Thankyou,
Fahad
View 12 Replies
View Related
Aug 19, 2006
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies
View Related
Jan 28, 2012
I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
View 5 Replies
View Related
Jul 20, 2005
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
View 1 Replies
View Related
May 12, 2015
In Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
View 10 Replies
View Related
Mar 17, 2008
I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.
Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime
Child Table: TBL_VERIFICATION
PK: Serial Number
Thanks,
Sean
View 1 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Jul 25, 2014
Basically i have three Tables
Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
View 1 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Oct 22, 2007
............child |parent|.............a1 |a | .............a2 |a |.............a11 |a1 |.............b1 |b |.............b11 |b1 |.............b111 |b1 |.............
Here is my table I want to get all childs of "a" ie {a1,a11,a2}I mean recursivelyie child,chid of child ,child of child of child ........etc up to any extentet the table containHow can i for mulate the select querry?
View 3 Replies
View Related
Jun 26, 2015
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
View 3 Replies
View Related
Jun 17, 2002
Good morning,
I have two tables PARENTTBL which is a parent table and CHILDTBL which is a
Child table . For each insert in the Parent table i want to insert values in the child Table but (when inserting in child table) i want the Field FILED2 which is a varchar one will receive the value of the PKEY int cancatenated with a sequnetial value for each new PKEY
FILED2 = Rtrim(LTreim(Str(PKEY))+ Sequential # . which is a
Sequential # by PKEY value .
Exemple : If i insert
100,A,B in PARENTTBL (100 is a PK value)
i want to insert in CHILDTBL the following data :
125,100-1
126,100-2
300,100-3
452,100-4
The first column is the Child PK and the second column is the FILED2
I want to do this in one insert instead of insert then Update.
I don't find the equivalent of Number(*) of SYBASE or ROWNUM of Oracle in order to do This .
Thinks to help me
View 3 Replies
View Related
Jul 20, 2005
Hi,-- ddl/*create table #tmp (col1 int);insert into #tmp values(1);insert into #tmp values(2);insert into #tmp values(3);create table #tmpChild (col1 int, fkCol int, Num int);insert into #tmpChild values(1,1,3);insert into #tmpChild values(2,1,2);insert into #tmpChild values(3,2,1);*/-- get parent and child data (outer join)select *from #tmp t Left Outer JOIN #tmpChild tCON t.col1 = tC.fkCol-- resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 31 2 1 22 3 2 13 NULL NULL NULL(4 row(s) affected)*/-- desired resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 3-- eleminate next row, want only Max(Num) row from the child tbl withsame FK (parentID)-- 1 2 1 22 3 2 13 NULL NULL NULL*/In other words, desired results is as follows:/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 32 3 2 13 NULL NULL NULL*/How to accomplish this task? ENV: MS SQL Server 2000TIA
View 1 Replies
View Related
Feb 2, 2005
Hello there,
I need to get the last value (status) from a child table. I try to simplify the problem with the following example.
Create Table Users
(
UserId int,
Lastname nvarchar(50)
)
Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)
Create Table Status
(
StatusId int
Status nvarchar(50)
)
A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:
Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2
Will not return the wanted records because all authorized Users have been in this status.
I hope you understand the problem and can help me out.
Thx in advance.
Etinuz
View 3 Replies
View Related
Mar 23, 2014
Below is my sample data of my table named "Groups"
Code:
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
select * from groups;
Expected result:
Code:
with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all
select 'Microsoft' as GroupName,'Nokia' as SubGroup union all
select '' as GroupName,'EShop' as SubGroup union all
select 'IBM' as GroupName,'Meiosys' as SubGroup union all
select '' as GroupName,'UrbanCode' as SubGroup )
select * from ExpectedResult;
some sample query to how to achieve this parent-child has the same table.
View 9 Replies
View Related
Apr 4, 2008
Hello all gurus out there.
I'm an entre level junior programmer. My question is kind of confusing but I'll try to put it as simple as I can.
First we have a main table called "job1". This table consists the order information. The file_id is the unique id and the primary key for this table. This table also pertains other information such as customer data (max limit 5), job data etc. This table is actively (non-stop) used throughout the day.
We have a non-interactive process which will take customers information from the main table and insert into the child table table "jobcust". Jobcust would have file_id, cust, cust_type. For example, if Job1 table had fiel_id=100 and cust1="Tom" and Cust2="David", now Jobcust will have two records file_id, cust1 and file_id,cust2. The main problem is the child table needs to be updated right away and our non-interactive process is good at doing that.. but it is causing a major DATA LATENCY. I would like to ask you all, if you know any better way of doing this without any process.. like in the back end with a trigger/procedure or something like that.
All you help is appreciated.
Thanks much.
View 5 Replies
View Related
Jun 13, 2008
Hi
I have a single table which holds a hierarchical a structure. I would like to get all the child ID's from one Stored Proc call. For example I give it an ID in the structure and I would like to give me all the child records that exist to the lowest level for each category found
My table structure is
[SvcID] [varchar](30) NOT NULL,
[Ser_SvcID] [varchar](30) NULL,
[SvcDesc] [varchar](100) NOT NULL
I know where the root of the hierarchy is because Ser_SvcID will be null. Im not sure how to do this in one stored procedure call
Hope the above makes sense
Many thanks in advance
View 2 Replies
View Related
Feb 28, 2008
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
View 3 Replies
View Related
Mar 13, 2008
Hi
i have a table named categorymaster
categoryid catname parentid
1 Boxing 0
2 Tennis 0
3 Basketball 0
4 MayWeather 1
5 Tyson 1
6 Clinton woods 1
7 RogerFederer 2
8 Micheal 3
9 Hingis 2
so if i give input say categoryid=1[This falls under main category-boxing]
i need to get result as
1 boxing [main category]
4 mayweather [sub category]
5 tyson [sub category]
6 clinton woods [sub category]
if i give categoryid=5[Note:Tyson]
result should be as
1 boxing [main category]
5 tyson [sub category]
hope u can get my question
Thanks in advance
View 2 Replies
View Related
Jul 19, 2006
hi all, can anyone help me?I am a relative newbie to sql server and I am more familiar withEnterprise Manager than QA. I have made many many access databasesthough. I am making an asp.net application where by there are a setnumber of users, about 80, each one logs in and manages informationwithin their department.To get them started a manager has written 10 different hazards thatwill apply to all of the departments, and he has written consequencesand controls for the hazards. Each department must have thisinformation as each will manage and deal with them differentlyThe hazard information is stored in a main 'hazards' table, and theconsequences and controls are stored in related tables linked by the'hazardID' from the main table to a foreign key 'hazardID' in therelated tablesWhat i want to know is if there is a relatively simple way of using aquery to populate the 10 hazards to each department, and to alsoinclude the related table links, i dont mind renaming the departmentsnames to match each hazard, but i do not want to have to relink therelated tables manuallyIf anyone can give me any advice to get me started i will be incrediblygratefulthank youTable information is belowHazards------------HazardID - identity key fieldHazard - varcharDepartment - varcharConsequences----------------------ConsequenceID - identity key fieldHazardID - FKConsequence - varcharControls------------ControlID - identity key fieldHazardID - FKControl - varchardwight
View 3 Replies
View Related
Jul 20, 2005
I want to create a 1-many relationship. Parent table has a primarykey, child table has no primary key. The child table does have anindex with all four fields of the parent's PK. How can I do this?Thanks, Bob C.
View 9 Replies
View Related
May 15, 2007
I have a situation here. Please
advice me on this.
I have a master table and a child
table. They have a PK and FK relationship. The master table has an identity
column with auto increment set to true. This map as a FK in the child table. My
questions are:
Can I have a single form to insert a new record into
master and child table at the same time?This has to be accomplished without stored
procedures. Can it be done?Is it possible to do this with a single insert query?
If yes, can it be done with sql data source or
dataset or tableadapters’?Please point me towards appropriate link for doing
so.
Thank you.
I am currently using SQL Server
2005 and VS 2005.
View 1 Replies
View Related
Aug 23, 2007
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table.
Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted?
How can I achieve this?
View 3 Replies
View Related
Feb 4, 2005
hi,
i have two tables with parent/child relationship - pipeline and pipelineStatus. the select statement like this:
SELECT *
FROM pipeline INNER JOIN
pipelineStatus ON pipeline.id = pipelineStatus.parentID
i got multiple records for each pipeline.id because of multiple records of pipelineStatus. Is it possible to get only one record for each pipeline.id with last record of pipelineStatus table?
(stored procedure ok)
thanks advance for answering my question.
View 7 Replies
View Related
Mar 23, 2014
Below is my sample data of my table named "Groups"
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
[Code] ....
Expected result:
with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all
[Code] ....
How to achieve this parent-child has the same table.
View 5 Replies
View Related
Feb 5, 2008
Hi
I wanted to use the table variable in Stored proc , for that i have create the table variable in the main SP which will be used by again called sp(child SPs)
now when i am trying to use the same table variable in the child SP, at the time of compliation it is showing error
Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149
Must declare the table variable "@tmp_get_imu_retn".
Can any body give me the idea how to complile the child SP with the same table variable used in the main SP.
Thanks,
BPG
View 11 Replies
View Related
Jul 20, 2005
There are two tables, parent and child table.Foreign key field of the child table is not a required field. If thedata is submitted w/o a value into the foreign key field, what shouldI make it as a default value? Should it be "null"?
View 2 Replies
View Related
