June 24, 2015 at 4:12 am
Hello to everyone,
I've got this problem with this exercise apologize that we insert into the table friends that at start is empty some couple of friends
Table Friends:
+------------+------------+
| Friend1 | Friend2 |
+------------+------------+
| Marc | Luc |
| Luc | Marc |
| Marc | John |
| John | Marc |
+-------------------------+
The table Relationship at the beginning is empty
+--------------------------------+-----------+---------+
| GradeOfRelationShip | Friend1 | Friend2 |
+--------------------------------+-----------+---------+
| | | |
+--------------------------------+-----------+---------+
After the insert of the tuple of friends I'll see something like this
+--------------------------------+-----------+---------+
| GradeOfRelationShip | Friend1 | Friend2 |
+--------------------------------+-----------+---------+
| 1 | Marc | Luc |
| 1 | Luc | Marc |
| 1 | Marc | John |
| 1 | John | Marc |
+--------------------------------+-----------+---------+
I inserted into Relationship only the couple of friends directly (in table Friends I insert only direct friends so with Grade 1)
Then I need to find all possible couple of friends from what I inserted into relationship so the output will be:
+--------------------------------+-----------+---------+
| GradeOfRelationShip | Friend1 | Friend2 |
+--------------------------------+-----------+---------+
| 1 | Marc | Luc |
| 1 | Luc | Marc |
| 1 | Marc | John |
| 1 | John | Marc |
| 2 | Luc | John |
| 2 | John | Luc |
+--------------------------------+-----------+---------+
So what my trigger must do:
After the insert of a couple of friends into table Friends I need to check if this couple already exist into table relationship.
If exists with GradeOfRelationship = 0 I need to set this GradeOfRelationship with the new value = 1 (now I've got a path between this friends)
If not exists i need to insert this tuple into the table Relationship with GradeOfRelationship = 1
I must search all possible path between all friend that I've inserted into Relationship, for do that i think that it's necessary a Join like this
Select
Table1.Friend1, Table1.Friend2, Table1.GradeOfRelationship,
Table2.Friend1, Table2.Friend2, Table2.GradeOfRelationship
From
Relationship Table1
join
Relationship Table2 on Table1.Friend2 = Table2.Friend1
And the output that i think that I'll see:
+-------------------+------+-----------+---------+-------+
| Friend1 | Friend2| Lv | Friend1 | Friend2 | Lv |
+-------------------+------+-----------+---------+-------+
| Marc | Luc | 1 | Luc | Marc | 1 |
| Marc | John | 1 | John | Marc | 1 |
| John | Marc | 1 | Marc | Luc | 1 |
| Luc | Marc | 1 | Marc | John | 1 |
+--------------------------------+-----------+-----------+
So I'll be interested only with the tuple in which
Table1.Friend1 < > Table2.Friend2
and Table1.Friend1 < > Table2.Friend2 not exist in RelationShip
Insert into Table Relationship Values (Table1.Friend1,
Table2.Friend2,
Table1.GradeOfRelationship + Table2.GradeOfRelationship)
So now there is the main problem, the recursive function that after every insert on table Friends will do this. And the other problem is that i need to insert into GradeOfRelationship the minimum path between friends. If there is not any relationship between two people i must insert into table Relationship the couple of friends with grade 0
I trying with SQL Server but at the moment i don't have any idea to how do this statement, i think that i must use some recursive function but i don't know how....i tried to write CTE but it don't work like i want
With Table1 as (Select Friend1,Friend2, 0 as Level
from Friend
Union All
Select F1.Friend1, Table1.Friend2, Level+1 as FriendshipLevel
From Friend F1 join table1 on F1.Friend2=table1.Friend1
where F1.Friend1 < > Table1.Friend2
)
Select *
From Table1
This SQL query didn't work well because it start to all possible computation with friends so 0 to infinite so it go on no termination.
Thanks a lot for your cooperation and looking forward into your answer
Best Regards
June 24, 2015 at 7:49 am
Have you looked at using MERGE INTO?
https://msdn.microsoft.com/en-us/library/bb510625(v=sql.110).aspx
The article states, "Performs insert, update, or delete operations on a target table based on the results of a join with a source table." Sounds like what you want, Friends is your source table, and Relationship is your target table, and you want to update Relationship based on joins between the two.
Also, the article states later, "The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics."
There is also a paragraph on triggers - it may not be suitable if your Relationship table also has triggers.
June 24, 2015 at 7:59 am
g5808599 (6/24/2015)
Hello to everyone,0
down vote
favorite
I've got this problem with this exercise apologize that we insert into the table friends that at start is empty some couple of friends
Table Friends:
+------------+------------+
| Friend1 | Friend2 |
+------------+------------+
| Marc | Luc |
| Luc | Marc |
| Marc | John |
| John | Marc |
+-------------------------+
The table Relationship at the beginning is empty
+--------------------------------+-----------+---------+
| GradeOfRelationShip | Friend1 | Friend2 |
+--------------------------------+-----------+---------+
| | | |
+--------------------------------+-----------+---------+
After the insert of the tuple of friends I'll see something like this
+--------------------------------+-----------+---------+
| GradeOfRelationShip | Friend1 | Friend2 |
+--------------------------------+-----------+---------+
| 1 | Marc | Luc |
| 1 | Luc | Marc |
| 1 | Marc | John |
| 1 | John | Marc |
+--------------------------------+-----------+---------+
I inserted into Relationship only the couple of friends directly (in table Friends I insert only direct friends so with Grade 1)
Then I need to find all possible couple of friends from what I inserted into relationship so the output will be:
+--------------------------------+-----------+---------+
| GradeOfRelationShip | Friend1 | Friend2 |
+--------------------------------+-----------+---------+
| 1 | Marc | Luc |
| 1 | Luc | Marc |
| 1 | Marc | John |
| 1 | John | Marc |
| 2 | Luc | John |
| 2 | John | Luc |
+--------------------------------+-----------+---------+
So what my trigger must do:
After the insert of a couple of friends into table Friends I need to check if this couple already exist into table relationship.
If exists with GradeOfRelationship = 0 I need to set this GradeOfRelationship with the new value = 1 (now I've got a path between this friends)
If not exists i need to insert this tuple into the table Relationship with GradeOfRelationship = 1
I must search all possible path between all friend that I've inserted into Relationship, for do that i think that it's necessary a Join like this
Select
Table1.Friend1, Table1.Friend2, Table1.GradeOfRelationship,
Table2.Friend1, Table2.Friend2, Table2.GradeOfRelationship
From
Relationship Table1
join
Relationship Table2 on Table1.Friend2 = Table2.Friend1
And the output that i think that I'll see:
+-------------------+------+-----------+---------+-------+
| Friend1 | Friend2| Lv | Friend1 | Friend2 | Lv |
+-------------------+------+-----------+---------+-------+
| Marc | Luc | 1 | Luc | Marc | 1 |
| Marc | John | 1 | John | Marc | 1 |
| John | Marc | 1 | Marc | Luc | 1 |
| Luc | Marc | 1 | Marc | John | 1 |
+--------------------------------+-----------+-----------+
So I'll be interested only with the tuple in which
Table1.Friend1 < > Table2.Friend2
and Table1.Friend1 < > Table2.Friend2 not exist in RelationShip
Insert into Table Relationship Values (Table1.Friend1,
Table2.Friend2,
Table1.GradeOfRelationship + Table2.GradeOfRelationship)
So now there is the main problem, the recursive function that after every insert on table Friends will do this. And the other problem is that i need to insert into GradeOfRelationship the minimum path between friends. If there is not any relationship between two people i must insert into table Relationship the couple of friends with grade 0
I trying with SQL Server but at the moment i don't have any idea to how do this statement, i think that i must use some recursive function but i don't know how....i tried to write CTE but it don't work like i want
With Table1 as (Select Friend1,Friend2, 0 as Level
from Friend
Union All
Select F1.Friend1, Table1.Friend2, Level+1 as FriendshipLevel
From Friend F1 join table1 on F1.Friend2=table1.Friend1
where F1.Friend1 < > Table1.Friend2
)
Select *
From Table1
This SQL query didn't work well because it start to all possible computation with friends so 0 to infinite so it go on no termination.
Thanks a lot for your cooperation and looking forward into your answer
Best Regards
Sounds to me like a design nightmare. The last place you'd want a query of this nature is in a trigger. Over time, even if initial performance was ok, as the number of friends with linkages increases, performance would go to heck in a hand-basket in rather short order. I do not believe for a fraction of a second that this design is sustainable. Understand that the trigger might well be inherently infinite, as every insert triggers another insert, ad infinitum. I can't imagine this is supportable in any viable way. What's the driving force behind this requirement? Seems to me that deriving the relationships here is entirely too loose, as there's no directional nature to any given relationship. Traversing a heirarchy is a reasonably well known methodology that can be reasonably translated into T-SQL, and is best with establishing the functional equivalent of a parent-child relationship. Storing an additional record to look at that same relationship as child to parent isn't really necessary. Please elaborate on the objective, and perhaps a better alternative can be found.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 8:09 am
before thanks for your answer. It's not infinite they can not be exist something like this (Two rows with the same friends but with a different GradeOfRelationship)
Luc Marc 1
Luc Marc 10000
I always consider the minimum GradeOfRelationship. So the problem will not go on "no termination".
My problem is how calculate the path between two people.
You must consider the problem like in a Social Network. Consider all Friends like node and GradeOfRelationship like path between two nodes.
So it's impossible that this problem will be infinite 🙂
June 24, 2015 at 8:34 am
g5808599 (6/24/2015)
before thanks for your answer. It's not infinite they can not be exist something like this (Two rows with the same friends but with a different GradeOfRelationship)Luc Marc 1
Luc Marc 10000
I always consider the minimum GradeOfRelationship. So the problem will not go on "no termination".
My problem is how calculate the path between two people.
You must consider the problem like in a Social Network. Consider all Friends like node and GradeOfRelationship like path between two nodes.
So it's impossible that this problem will be infinite 🙂
Okay, but why represent a given relationship with 2 records instead of one? Also, you might want to consider that the trigger itself is doing an insert, which will then trigger itself, so keep that in mind. Even with just one record per relationship, as the network of friends grows, this is going to get ugly in one heck of a hurry.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 8:35 am
Gosh this looks familiar. http://stackoverflow.com/questions/31011562/sql-trigger-with-recursion
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2015 at 8:55 am
Sean Lange (6/24/2015)
Gosh this looks familiar. http://stackoverflow.com/questions/31011562/sql-trigger-with-recursion
Yep, sure does... There are no really good, readily available, solutions for a problem of this nature. It takes the resources of a company like Facebook or Google to keep track of relationships in a way that can truly scale, and you can be quite sure their queries and/or algorithms for handling it are considered "IP", and protected from disclosure. Even so, it's highly likely that there's only one record to represent a given relationship.
That said, it doesn't mean there aren't any viable solutions here. It's just that a trigger and multiple records per relationship is a quick path to disaster. Computing the dstance for a given relationship as needed may make more sense than trying to pre-compute it in a trigger. It also makes me wonder what the plan is for when a relationship is dissolved ? What then? That too, could get really ugly.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 8:59 am
This is an excercice that in past will done by student in an exam of DataBase. I don't have the solution of my teacher and so i would like to resolve it and learn how i can resolve problem like this if i ll receive something similar in the exam that i ll do next month.
MY teacher wanted that the problem was resolved by using trigger with an incremental solution.
So after every insert into table friends calculate all possible path between friends and insert into table Relationship the minimum path between two friends.
June 24, 2015 at 9:05 am
g5808599 (6/24/2015)
This is an excercice that in past will done by student in an exam of DataBase. I don't have the solution of my teacher and so i would like to resolve it and learn how i can resolve problem like this if i ll receive something similar in the exam that i ll do next month.MY teacher wanted that the problem was resolved by using trigger with an incremental solution.
So after every insert into table friends calculate all possible path between friends and insert into table Relationship the minimum path between two friends.
No offense to your teacher but this is a horrible lesson. The real world implications of this type of thing is horrendous. A trigger really should not be used for this sort of thing. Triggers should be used very sparingly and a recursive cte inside a trigger is a recipe for horrible performance. If I had to do something like this I would create a view that can determine the relationships. Keeping this type of information in a persistent table is a nightmare. You have to always make sure it is up to date for every single insert, update, delete. Yuck!!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2015 at 9:15 am
i know that it can be an horrible situation but i need to learn how to do this thing with trigger ;(
if you can help me also it is an horrible thing I shall be grateful, if is not possible no problem.
Thanks a lot for your cooperation
June 24, 2015 at 9:36 am
g5808599 (6/24/2015)
i know that it can be an horrible situation but i need to learn how to do this thing with trigger ;(if you can help me also it is an horrible thing I shall be grateful, if is not possible no problem.
Thanks a lot for your cooperation
To be honest, this falls in the category of things best not known. It's abundantly clear that the entire design is so full of problems that there's no useful solution that could ever be maintained. What were you planning on doing for a DELETE ? I'd also conclude that your teacher may need to have an exam... to figure out why he or she insists on trying to get students to learn how to do things you should never do. I, for one, will not post code that I know should never be used. I wouldn't want my reputation to be associated with a total piece of junk. There are more appropriate things to learn than how to do the wrong thing on purpose.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 9:40 am
As you want. I just asked help for a problem that i can't resolve alone.
Don't worry
Thanks
bye bye
I think that you can close the thread or delete definitly
June 24, 2015 at 9:53 am
g5808599 (6/24/2015)
As you want. I just asked help for a problem that i can't resolve alone.Don't worry
Thanks
bye bye
I think that you can close the thread or delete definitly
No no. Let's turn this into a learning opportunity. The trigger is just an awful idea here but let's figure out how to create the query that you are after anyway.
Let's start with your existing sample data:
CREATE TABLE Friends
(
Friends1 varchar(50),
Friends2 varchar(50),
primary key(Friends1, Friends2)
);
INSERT INTO Friends
(Friends1, Friends2)
VALUES
('Luc', 'Marc'),
('Marc', 'Luc'),
('John', 'Marc'),
('Marc', 'John'),
('Alex', 'Marc'),
('Marc', 'Alex');
CREATE TABLE Relationship
(
Friends1 varchar(50),
Friends2 varchar(50),
GradeOfRelationship int
primary key(Friends1, Friends2)
);
Let's forget about the insert and the trigger nonsense of this. If there is a new row say "Sean" and "Steve" what should the select statement return? What about if the new row would be "Sean" and "Alex"?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2015 at 9:57 am
ve got this problem with this exercise
maybe useful to see the original exam question....just a thought
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 24, 2015 at 10:06 am
If there is a new row with Sean - Steve. i must do these things:
1) I must insert into table Friends the symmetric value (Sean - Steve) ----> And is not a problem
2) I must check into table Relationship if there is a row with value (Sean - Steve).
if not exists insert into Relationship value (Sean, Steve, 1) I insert 1 because i've got a direct path.
if already exists i must check is GradeOfRelationship value if is = 0 or >1 then i ll set GradeOfRelationship = 1
If there is a new row with Sean - Alex i must do the same thing for symmetric into table Friends.
Into table relationship i suppose a join like this for search all possible path when there is a friend in common.
Select
Table1.Friend1, Table1.Friend2, Table1.GradeOfRelationship,
Table2.Friend1, Table2.Friend2, Table2.GradeOfRelationship
From
Relationship Table1
join
Relationship Table2 on Table1.Friend2 = Table2.Friend1
After this i think that i need to insert into table Relationship (only in this table not in table Friends!) all new row that at the moment
not exist into table Relationship.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply