SQL 2012 :: Junction Table In Many To Many Relationship
May 5, 2014Why do we need a junction table in a many to many relationship? Why can't everything be in just 2 tables?
View 9 Replies
ADVERTISEMENT
Insert Statement For Junction Table On Many To Many Relationship Ms Sql Server 2005
Oct 10, 2007Hello,
This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here...
http://msdn2.microsoft.com/en-us/library/ms178043.aspx
At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools).
http://msdn2.microsoft.com/en-us/library/ms189098.aspx
and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW....
Here is how I set everything up...
PetitionSet table consists of:
PetitionSetID int auto-increment primary key
PetitionSetName varchar(50) no nulls
PetitionSetScope varchar(50) no nulls
the Petition table consists of:
PetitionID int auto-increment primary key
PetitionSetID int no nulls
PetitionName varchar(50) no nulls
the SetToPetitionJunction table consists of:
PetitionSetID int
PetitionID int
And, there is a composite key made up of both the PetitionSetID and PetitionID fields.
I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition.
The trigger is on the Petition table and it has the following code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER .[SetToPetitionJunctionTrigger]
ON .[dbo].[Petition]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO SetToPetitionJunction
(PetitionID, PetitionSetID)
SELECT Petition.PetitionID, PetitionSet.PetitionSetID
FROM Petition INNER JOIN
PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID
END
I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity...
The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table.
Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error...
Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'.
The statement has been terminated.
David
All Rights Reserved in All Media
How To Write A Select Query To Return Records In A Many-to-Many Relationship - Junction Table???
Oct 26, 2006Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies View RelatedSQL Server 2012 :: Query To Generate Relationship (Parent Child Hierarchy From A Table)
Jul 18, 2015I am working on a query to generate parent child hierarchy from a table.
Table has below records.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
[Code] ...
how to achieve this.l tried with temp tables it doesn't work.
Junction Table SQL Sentence - Please Help Me Out
Feb 17, 2006I have two tables. A table called users (content speaks by it self) and a table called groups.
Since i want every user to be able to be a member of several groups and, of course, a group to have several users i have made a junction table called jt.
The junction table contains userId's and groupId's according to the user-group bindings. The primary key(identity) in the junction table is a int named jtId.
Now i want to take out all posts from the junctiontable and the corrresponding userName (s) from the users-table and the corresponding groupName from the groups-table.
Can somebody please help me to make a SQL command that will di that for me. I have tried with INNER JOIN and several SELECTS in the same command.
Thanks in advance, Greetings from Esben
Junction Table/ Many To Many Relationships
May 9, 2007I am trying to update 2 tables at the same time by adding new records to them and then making sure that they are related on my junction table?
Table1
packageID
Packagename
Table2 (JunctionTable)
PackageID
JobID
Table3
JobID
JobName
And I want to create a new package and new Job Name at the same time I would need to make multiple insert statements
First take care of the new Package
INSERT INTO Table1 (PackageName) Value (@PackageName)
Then Take Care of the JobName
INSERT INTO Table3 (JobName) Value (@JobName)
Finally marry the two together
but how?
INSERT Among 3 Tables And 1 Is A Junction Table
Sep 23, 2007Looking for some help here, so thanks for any input. I'm a painfully new newbie to SQL scripting.Situation:
I have a simple database to handle an organization's events. Those
events are categorized and may have more than one category assigned to
each event. I need a maintenance Web Form to update their events.Set
Up (so far): I have a CATEGORIES table. It has an auto incrementing UID
and a Category name field. This table will be updated so infrequently,
I plan to update it manually (no need for a maintenance Web Form). Next
is the EVENTS table. It also has an auto incrementing UID along with
several fields (Title, Location, DateTime, etc.). The junction table is
named jEVENTSCATEGORIES. It has its own auto incrementing UID along
with 2 fields named for the primary keys (UIDs) in the other 2 tables
(EventsID and CategoryID).Goal: On the Web Form, I have a
CheckBoxList control that's populated by the CATEGORIES table. One or
more categories can be checked for each event. I have a FormView
control that allows Edits and Inserts.Need: I need to know the
INSERT statement(s) required to insert a new record in the EVENTS table
and then to update one or more rows in the junction table
(jEVENTSCATEGORIES).My Assumptions: I know how to create
SELECTs and INSERTs and whatnot, but I'm not certain how to create a
second INSERT statement that is based on a variable (or output) from a
previous action. So any help would be MUCH appreciated. Thanks for your
time!
Retrieving Data From A Junction Table
Jan 30, 2008Hi i have a junction table(UserGroups) which is linking my users table with my groups table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: MUFC
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC
Below is my SQL statement;
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
Junction Table Design Options
Jul 20, 2005As an example, I am building an authentication mechanisim that will usedata in the 3 left tables to determine rights to objects in adestination table, diagrammed below. In this structure, multiplerecords in the left tables will point to multiple records in the righttable. Normally, I would approach this problem using junction tables(LeftID, RightID) to create many-to-many joins.However, given the structure of each table is nearly identical (as faras the linking IDs are concerned), I could also use a single junctiontable with columns for each available table ID (LeftID1, LeftID2,LeftID3, RightID). In this table, only two IDs would be utilized perrow (LeftIDx -> RightID).In both designs, the needed rights information is returned from fairlysimple views, thus the end result is equivalent. The advantage to thesecond, multi-ID junction table design, is a simpler databasestructure. However, never using this approach before, I am unsure ofthe potential future performance impacts.Any significant downsides to this second design? Examples of anabbreviated structure follow:Data Tables-----------LeftTable1LeftID1 (int)Data1LeftTable2LeftID2 (int)Data2LeftTable3LeftID3 (int)Data3DestinationTableRightID (int)DataLinking tables option 1-----------------------JunctionTable1LeftID1RightIDJunctionTable2LeftID3RightIDJunctionTable3LeftID3RightIDLinking table option 2----------------------JunctionID1 (int)ID2 (int)ID3 (int)DestinationID (int)
View 1 Replies View RelatedStopping Duplicate Values From A Junction Table
Feb 28, 2008Hi i have a junction table(UserGroups) which is linking my "users" table with my "groups" table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change.
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: MUFC
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC
Below is my SQL statement;
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
Stopping Duplicate Values From A Junction Table
Mar 1, 2008Hi i have a junction table(UserGroups) which is linking my "users" table with my "groups" table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change.
UserName: Edwin Carols
UserAge: 28
JobTitle: Manager
GroupName: MUFC
UserName: Edwin Carols
UserAge: 28
JobTitle: Manager
GroupName: AFC
Below is my SQL statement;
Code:
SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
And my table structure is like;
Users; UserID, UserName, UserAge
Groups; GroupID, GroupName
UserGroups; UserGroupID, UserID, GroupID
Junction/link Table Primary Key: Best Practice Using SQL Server
Nov 30, 2005Hi all,
This is a bit of a general question regarding SQL Server link tables that i hope someone can find the time to answer.
I am looking for the best practice. If I have 3 tables
1) tblCompetition
2) tblTeams
3) tblTeamsInCompetition (this is the link table)
I know the link table should have a related CompetitionID and TeamID,
but should the link table also have a primary key that is an identity
autoincrement by 1 just as you would with the other two tables. In
Access I have never done this, however in SQL Server I have read that
you should do this (but now I can't find the documentation again which
prompts me to ask the question here).
Thanks for taking the time to answer this question.
SQL Query Question : How To Add Data To Two Tables Connected By Third Junction-table.
May 1, 2008
Hello specialists.
Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.
Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E
How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within junction-table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.
The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo
Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison
Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C
4 Group_E
Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4
I hope you can help me cracking this nut.
Thx in advance. Greetings Fred
SQL 2012 :: Schema Without FK Relationship?
Apr 18, 2014I Just checking a newly deisgned db at my new place, AND see there is NO FK contraints EXISTS FOR many tables, so you can enter:
INSERT INTO tProdValues (Value, CategoryID) rows with any CategoryID (eg. = 99),
even they don't exist in tCategoryTypes, is this OK?
SQL 2012 :: Create View From Multiple Tables That Have 1 To M Relationship
Jul 31, 2014I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
Why I Got The Message As You Need To Create The Many-to-one Relationship Between The Case Table And The Nested Table?
May 30, 2007Hi, all experts here,
Thank you very much for your kind attention.
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?
Mar 25, 2006How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?
For example:
I have 2 tables, tbl1 and tbl2.
tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id
If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?
mradlmaier
Power Pivot :: Use Relationship Not Overriding Conflicting Active Relationship?
Oct 14, 2015I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.
Operation (Commodity, OperationKey) ==========>
Commodity (Commodity)
/
/
|
|
Advice (OperationKey) ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)
I have the following measure:
Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
Table Relationship
Apr 5, 2007Hello, I created some SQL 2005 tables using Microsoft SQL Server Management Studio. I need to get the script code of those tables. I was able to do that by right clicking over each table. But how can I get the code for the relationships between the tables? Can't I create relationships between two tables by using T-SQL? Thanks, Miguel
View 3 Replies View RelatedWhat Relationship To Use Between These Two Table?
Feb 7, 2008Hi I have two tables:
1.) Operator-OperatorID{PK, int, not null}-OperatorName{varchar(100), not null}-Enabled{bit, not null}-PasswordChange{bit, not null}-BirthDate{datetime, not null}
2.) Password-PasswordID{PK, int, not null}-Password{varchar(50), not null}-ExpirationDate{datetime, not null}
I'm not sure how to design and layout these two tables. The layout of these two tables is completely flexible as the application has not been deployed. I'm open to any good suggestions.
For each Operator I want to stored up to 3 previous passwords plus their current password. The password change field is so that if the operator's password expires or gets reset, they will be forced to enter a new password. This is a simple internal company application, so password encrypting is not necessary. The ExpirationDate indicates the date that the password will expire.
Hope to hear from someone soon! Thank you!
Define More Than One Relationship Per Table?
Apr 11, 2004Why is it not possible to define more than one relationship per table?
i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?
thank you
Relationship Inside The Same Table
Sep 19, 2005i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation
View 4 Replies View RelatedNaming Of Relationship Table
Jun 10, 2006We have two tables. Users and Projects and there is a many-to-many relationship.Ex. A user can be assigned into multiple projects.For the relationship table, should the table name be UserProjects or ProjectUsers?Also should it be singular or plural? (ex. UsersProjects or ProjectsUsers)?
View 2 Replies View RelatedCreate Table With Many-to-many Relationship...
Dec 5, 2006Hi, I come back again.
Can anyone help me to create table with many-to-many relationship. Here is my three tables
tbl_Networks
(
NID int identity(1,1) primary key,
NetworkName nvarchar(256)
)
tbl_Categories
(
CID int identity(1,1) primary key,
CateName nvarchar(256),
NID int
)
tbl_Sim
(
SID int identity(1,1) primary key,
NID int,
CID int,
NameOfSim nvarchar(256)
)
My problem is 1 value in tbl_Sim may have multiple values in table tbl_Categories and vice versal. And I don't know how to organise them
So I need some help...
Table Relationship. What Am I Doing Wrong?
Feb 27, 2008Hello,
I have 3 tables: Articles, ArticlesTags and Tags.
ArticleTags relate Articles and Tags records.
When I try to delete a record in Tags I get the following error:
The DELETE statement conflicted with the REFERENCE constraint
"FK_ArticlesTags_Tags". The conflict occurred in database "MyDB", table "dbo.ArticlesTags", column 'TagID'.
The statement has been terminated.
What am I doing wrong?
Here is how I am creating my tables:
create table dbo.Articles
(
ArticleID uniqueidentifier not null
constraint PK_Article primary key clustered,
Body nvarchar(max) not null
)
create table dbo.ArticlesTags
(
ArticleID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_ArticlesTags
primary key clustered (ArticleID, TagID),
constraint FK_ArticlesTags_Articles
foreign key(ArticleID)
references dbo.Articles(ArticleID)
on delete cascade,
constraint FK_ArticlesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(200) not null
)
Basically, what I need is:
1. If an Article is deleted then:
> Delete all records for that Article in ArticlesTags
> Don't delete any Tag in Tags.
2. If an Tag is deleted then:
> Delete all records associated with it in ArticlesTags.
> Don't delete any Article in Articles.
What am I missing?
Thanks,
Miguel
Table Relationship In VB Express
Dec 19, 2006I
have 3 tables I am trying to relate for a music player. I was
following the example in the msdn however, my relationships do not seem
right. Here are tables i want to relate:
Table 1:
ArtistID
ArtistName:
Table 2:
RecordingID
RecordingName
ArtistID
Table 3:
TrackID
TrackName
TrackNumber
TrackLocation
RecordingID
So
the main idea here is that the foreign keys are recordingId and
artistID. So what i did is created the 3 tables and then make a
diagram to create the relationships. I then was reading this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=219696&SiteID=1
What
i want to do is use 2 different text boxes lets say and as i move from
the records in the Record_table (2) the corresponding artist will
change with it. However in the dataset the relationship looks like
this:
Artist -> Recording -> Track.... inorder for me to get
this relationship to work correctly i have to change all the
relationships in the dataset diagram. This way the dataset would look
like this: Track -> Recording ->Artist. This way i can use the
2 bindings to reference each other as stated in the link above. why
doesnt the relationship of the database know this already? why do i
have to change the relationship in the datasets.
Problems Creating Second Relationship To The Same Table
Apr 6, 2007I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error:
- Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.
Search On Table With Recursive Relationship
Apr 15, 1999I am using tables with recursive relationships extensively. For example the table tbComponent has a primary key called Co_ID and a foreign key called Co_Co_ID which references the field Co_ID. This allows a component to have unlimited child components, and each child component can have an unlimited number of tiers of children. I have a few question for which I have seen no documentation on.
1. How can I create a view or a SP that will return a component record and all of its children and children's children records down to the last/lowest child record?
2. I need to be able to do a search in this table. Example:
Table: tbComponent
Columns: Co_ID Integer
Co_Co_ID Integer
Co_Name Text
Co_Attribute Text
Co_Category Text
Note: the data for Co_Category comes from a lookup table with also has a recursive relationship to itself where a category can have an unlimited number of tiers of children categories.
A typical group of records could be something like this:
Co_ID Co_Co_ID Co_Name Co_Attribute Co_Category
1 1 Car Blue Ford
2 1 Body Steel Parts
3 2 Door Front Parts
4 3 Invoice April 1 1999 Accounting
Ok, say there is over a million records in this table. Say I want to query this table and return all of the invoices for cars between March 1 1999 and May 1 1999. Say for example that there are less records where Co_Name has a value of Car then there are records with a value of Invoice, so logically I would set some kind of criteria to limit only invoices with where Co_Name = "Car". That's easy, I can return a result set of all the records Where Co_Name = "Car" and I can hold these in a view or a temp table. Now I need to query this View or temp table and see if it has any children records records Where Co_Name = "Invoice" . The problem is that the Invoice child could be a child record directly under the "Car" record, or 10 levels of children records down. The logic for this would be:
If Co_Name = "Car" Then Select * Where Co_Co_ID = 1, then take all of those record's values in Co_ID and run another statement Where Co_Co_ID = X and so on until there are no more children to search. If any of those records have a value of Invoice in Co_Name then return them.
Please don't give any speeches on Normal Relationship Database design suggesting a typical design using something like a Car table, a parts table, and a invoice table. I used the example above to demonstrate the dynamics of doing a search on a recursive relationship. I understand relationship theory and a recursive relationship is what we need to use in our situation.
Thanks for any and all input!
Create A View For One-to-many Relationship Table
May 8, 2006hi..
I would like to create a view for two tables which have a one-to-many relationship.
Code:
Table: Supplier
Supp_ID
1
Table:Supplier_category
Supp_ID,StockCategoryID
1,56
1,57
1,90
How can i create a view that has columns like below:
Supp_ID, Stock
1,[56,57,90]
Thanks in advance.
Using Same Table Having Parent And Child Relationship?
Mar 23, 2014Below 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.
Create Table With Recursive Relationship
May 16, 2006I am fairly new to SQL and I am currently trying to createa SQL table (using Microsoft SQL) that has a recursiverelationship, let me try to explain:I have a piece of Data let's call it "Item" wich may again contain onemore "Items". Now how would I design a set of SQL Tables that arecapable of storing this information?I tried the following two approaches:1.) create a Table "Item" with Column "ItemID" as primary key, somecolums for the Data an Item can store and a Column "ParentItemID". Iset a foreign key for ParentItemID wich links to the primarykey"ItemID" of the same table.2.) create separate Table "Item_ParentItem" that storesItemID-ParentItemID-pairs. Each column has a foreign key linked toprimary key of the "Item" Column "ItemID".In both approaches when I try to delete an Item I get an Exceptionsaying that the DELETE command could not be executed because itviolates a COLUMN REFERENCE constraint. The goal behind these FK_PKrelations is is that when an Item gets deleted, all childItems shouldautomatically be deleted recursively.How is this "standard-problem" usually solved in sql? Or do I inned toimplement the recursive deletion myself using storedprocedures or something ?
View 3 Replies View RelatedWant 1 To Many Relationship When Child Table Has No Primary Key
Jul 20, 2005I 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 RelatedHow Can I Get Primary Key Col And Other Two Cols In The Same Table Become One To One Relationship.
Jun 12, 2006For ex.
Table Match_List ( MatchID, UserID_A, UserID_B)
constraints like
MatchID primary key
UserID_A <> UserID_B
unqiue index (UserID_A,UserID_B)
but I wish to exclude duplicated rows like 1,1,2 & 2,2,1,
cause UserA to UserB, and UserB to UserA are the same thing.
How can I get this?