Hello Every One, I'm working on a project (C# and SQL Server) where the user can select some fields for a select output from multiple tables in the database.
I'm in front of the problem: how can I manage the joining problem between tables whom are not directly connected.
For Example Between: [Compte] and [Encaissement].
You can see Image Dor the Schema:
Schema of DB
What I have tried:
I tried a Mapping table to store every relationship between two table,
Image Of The Table.
And I maked Also a stored procedure that take 2 argument @table1 and @table2 for searching the relationship in the mapping table, it return one row if the 2 tables are directly connected or one rows if there is a link table between them.
Picture Of The Stored Procedure.
To Here all it's good and works fine, but I need a generic algorithm that can work with n ink between the tables in select query.
Thank You, I'll be very grateful for your help.
That Is The script:
= = = = = = = = = = = = = = = = = = = = = = = =
use master;
go
set dateformat DMY
go
if exists (select * from
sys.databases where name='INTERMEDIAIRE')
drop database INTERMEDIAIRE;
go
create database INTERMEDIAIRE;
go
use INTERMEDIAIRE;
go
create table [Authentication](
id int identity(1,1)
constraint pk_auth primary key,
username varchar(10) not null unique,
mdp varchar(20) not null
);
go
create table Conditions(evenement varchar(255) not null,
systeme varchar(255) not null,
tempTable varchar(max) not null,
constraint pk_events primary key(evenement,systeme));
go
create table Structures(id int identity(1,1)
constraint pk_conditions primary key,
champs varchar(max) not null,
[type] varchar(max) not null,
taille int not null,
nomFonctionnel varchar(max),
ref_event varchar(max),
sysTable varchar(max) not null);
go
create table Mapping(table1 varchar(255) not null,
table2 varchar(255) not null,
key1 varchar(max) not null,
key2 varchar(max) not null,
constraint pk_map primary key(table1,table2));
go
--Inserttion Of Records
insert into [Authentication] values('user1','pass1');
insert into [Authentication] values('user2','pass2');
go
insert into Conditions values('Facturation','Vectis','TmpFactVectis');
go
--the table [Structures] Will be filled from an Excel file in My Application(Does Not Matter for now)
--There are the datas If you need them
go
insert into Mapping values('Client','Facture','id_client','client');
insert into Mapping values('DetailFacture','Facture','facture','ref_facture');
insert into Mapping values('Encaissement','Facture','facture','ref_facture');
insert into Mapping values('Paiment','Facture','facture','ref_facture');
insert into Mapping values('Compte','Client','id_compte','compte');
insert into Mapping values('Auth','Compte','id_auth','auth');
go
--Create the stored procedure responsible of the mapping
create procedure sp_GetMapping
@t1 varchar(255),@t2 varchar(255)
as
BEGIN
--the cases when (table1=@t1 & table2=@tb) OR (table1=@t2 & table2=@t1)
if exists(select * from Mapping where (table1=@t1 and table2=@t2))
select * from Mapping where (table1=@t1 and table2=@t2)
else if exists(select * from Mapping where (table1=@t2 and table2=@t1))
select * from Mapping where (table1=@t2 and table2=@t1)
--End of the two cases
else
--the query responsible to find the link table between two tables (@t1 and @t2)
select * from Mapping
where ((table1=@t1 and table2=(select table2 from Mapping where table1=@t2)) or
(table1=@t2 and table2=(select table2 from Mapping where table1=@t1)))
or
((table2=@t1 and table1=(select table1 from Mapping where table2=@t2))or
(table2=@t2 and table1=(select table1 from Mapping where table2=@t1)))
or
((table1=@t1 and table2=(select table1 from Mapping where table2=@t2))or
(table2=@t2 and table1=(select table2 from Mapping where table1=@t1)))
or
((table1=@t2 and table2=(select table1 from Mapping where table2=@t1))or
(table2=@t1 and table1=(select table2 from Mapping where table1=@t2)))
END
go
select * from Authentication;
select * from Conditions;
select * from Structures;
select * from Mapping;
go
= = = = = = = = = = = = = = = = = = = = = = = =