SQLTeam.com | Weblogs | Forums

Relationship between records in the same table


#1

Hello,

I am trying to create a relationship table which stores the relationship between two records in the primary table.The relationship that i am trying to represent is like Cartesian relationship. So if code A is related to code B and C then Code B is related to A and C and Code C is related to B and A . I would really appreciate if someone can suggest best way to implement as this will effect the update and the insert query. Below is the table structure
Table Codes
pk_code_id ,code_name
a, name_a
b, name_b
c, name_c
Table Codes_Link
pk_auto_id pk_code_id_a pk_code_id_b
1 a b
2 a c
3 b a
4 b c
5 c a
6 c b

I would really appreciate how best to tackle this.
Many Thanks,
Teju


#2

Would someone able to help with this please


#3

Can you provide DDL, sample data and expected results? otherwise we'll just be guessing and going back and forth


#4

How about creating a table
Create table relationship
(
Related int ,
ColumnA int ,
ColumnB int ,
ColumnC int
)
Go

Data like this
1 ,1 ,1,1
0 , 1 ,1,1

Related column
1 means related
0 means not related

Will this help


#5

What are you trying to do ?
Purpose goal

How will you use the relationship s

Depending on that
Design table and columns
Multiple ways possible

Hope it helps

Example
If you want
A and B related

ColumnAB int ,
ColumnUse int ,

ColumnAB
0 means not related
1 means related

ColumnUse
0 means don't use
1 means use

ColumnAB. ColumnUse

  1. . ,0

This means
ColumnAB related but don't use


#6

Thank you Mike. I have listed the sample data in my question earlier. To further explain what i would like to achieve is as below

The Codes table is the main table which contains the details about all the codes and the codes_link table is relationship table which shows the linking between the codes if there is any in the Codes table as to what are the codes related to . So for example if code a is related to code b and code b is in turn related to code a. I have a display page which i query to show all the related codes for say for example code A. so my query will be a simple select statement

**select pk_code_id_b from Codes_link where pk_code_id_a="A"**

according to the sample data above for A it would show me b and c

I could use inner join with Codes here if i wanted to get some more details of code A from the Codes table.

The trouble would be to be insert data . In order to insert the relationship for B the values would be b to c and b to a . If there are more codes linked to B there would be more than 2 set of insert values.
insert into (pk_code_id_a,pk_code_id_b) values(a,b),(b,a)

But the real problem would be when i update the data , i will have to update all the related codes .

For both the insert and updates i cannot rely on users that they will insert/update data for all the Codes individually by going into every single Code record and update the related codes. I will have to come up with the insert statement which inserts all the related codes through one insert query as stated in the example above rather than going in the individual record and inserting .

Like wise for the update say for e.g. if code a's relationship gets changed to e then the record a would be updated to from a to b TO a to e and the (b,a) would be deleted and a new record would be inserted as e(e,a)

I need suggestions to implement this structure and in a optimised way. If i try to implement the way i have explained there will lot of duplicates in the relationship table.

I have tried to explain the scenario as much as possible.


#7

hi

teju .. i am trying to help you explain ...
:slight_smile:
:slight_smile:
hopefully i am correct

Codes table

CodeLinks table

select from codelinks
select pk_code_id_b from codelinks where pk_code_id_a='a'
image

join to code and get code info also
select a.,b. from codelinks a join codes b on a.pk_code_id_a = b.pk_code_id
where pk_code_id_a='a'

i have inserted 3 codelinks for b
Here d is not there in CODES table
have to add d to CODES table

one insert query
lot of duplicates in the relationship table.

**

solution ....

**
what you can do is with where clause
i think ..

>   update codelinks 
>         set pk_code_id_b = 'e'
>         where pk_code_id_a = 'a' 

Here are all the SQL's I used

all SQLs
use tempdb
go 

drop table Codes
go 

create table codes 
(
pk_code_id  varchar(1),
code_name varchar(10)
)
go 

insert into codes select 'a', 'name_a'
insert into codes select 'b', 'name_b'
insert into codes select 'c', 'name_c'
go 

select * from codes 
go 

select a.*,b.* from codelinks  a join codes b on a.pk_code_id_a = b.pk_code_id
where  pk_code_id_a='a'


drop table codelinks 
go 

create table codelinks 
(
pk_auto_id int identity(1,1),
pk_code_id_a   varchar(1),
pk_code_id_b varchar(1)
)
go 

drop table   codelinks
go 

insert into codelinks select 'a', 'b'
insert into codelinks select 'a', 'c'
go 


insert into codelinks(pk_code_id_a,pk_code_id_b) select 'b','a'
go 
insert into codelinks(pk_code_id_a,pk_code_id_b) select 'b','c'
go 
insert into codelinks(pk_code_id_a,pk_code_id_b) select 'b','d'
go 


select * from codelinks
go