Click here to Skip to main content
15,900,724 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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
/*
id	champs				type	taille	nomFonctionnel		ref_event	sysTable
==  ================    ======= ======  ================    =========== =============
1	ref_facture			int		5		IdFacture			Facturation	Facture
2	num_contrat_fact	int		6		Ncontrat			Facturation	Facture
3	dateFacture			date	10		DateFact			Facturation	Facture
4	id_client			int		6		Id_Acheteur			Facturation	Client
5	num_contrat_cli		int		6		Contrat_Client		Facturation	Client
6	nom					varchar	255		Nom_Client			Facturation	Client
7	type				varchar	15		Type_Client			Facturation	Client
8	qte					int		3		Qte					Facturation	DetailFacture
9	prix				decimal	15		Prix				Facturation	DetailFacture
10	tax					decimal	3		TVA					Facturation	DetailFacture
11	montant_paye		decimal	15		MtEncaissement		Facturation	Encaissement
12	date_operation		date	10		DtEncaissement		Facturation	Encaissement
13	type_operation		varchar	20		TypeEncaissement	Facturation	Encaissement
14	date_paiment		date	10		DtPaiment			Facturation	Paiment
15	montant_total		decimal	15		MtTTC				Facturation	Paiment
*/
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
= = = = = = = = = = = = = = = = = = = = = = = =
Posted
Updated 3-May-17 3:35am
v3
Comments
CHill60 2-May-17 12:31pm    
Instead of posting a picture of the SP off-site, just post the code here. Same with your data. You might find my solution to this post Get the mapping tables between two tables.[^] helpful
Anticonformiste97 2-May-17 16:14pm    
Thank you Chill , but it was not helpful, I don't undesrtood it enough
Andy Lanng 2-May-17 15:43pm    
Yus - As CHill says, post the code here where you can. Great for the Schema image, but if we want to test then it's best to let us copy/paste. Everyone here is here voluntarily and making us type more than we need to discourages interaction.

If you want perfect interaction then you could even post a script to create the tables, relations and test data. More work for you but it's one persons extra mile verses everyone else's extra mile. Besides, hopefully you know how to script that from your existing database. It's easier for your you, 1 person, than it is for all of us to each try the same. ^_^

PS: welcome. We're here to help :Þ
Andy Lanng 2-May-17 15:46pm    
Oh. One more thing... What tool did you use to create the procedure. It is extremely inefficient. It might be quick now it is is sooo un-scalable for larger datasets
Anticonformiste97 2-May-17 16:17pm    
Sql server management studio, I wrote it manually, please if you can give me some explanations to improve it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900