2 tables and one junction table

Transact-SQL
1

I'm not sure if it possible
I have 3 tables
CREATE TABLE [tbo].[tblIndicateurDist](
[IndicateurIDDist] [int] IDENTITY(1,1) NOT NULL,
[TitreIndDist] varchar NULL,
[ObjetID] [int] NULL,
CONSTRAINT [PK_tblIndicateurDist] PRIMARY KEY CLUSTERED

CREATE TABLE [tbo].[tblAnneeTBO](
[AnneeId] [int] IDENTITY(1,1) NOT NULL,
[Annee4Chiffre] varchar NULL,
[Annee8Chiffre] varchar NULL,
[Ordre] [int] NULL,
[Affiche] [bit] NULL,
CONSTRAINT [PK_tblAnneeTBO] PRIMARY KEY CLUSTERED

CREATE TABLE [tbo].[tblIndicateurXAnneeDist](
[AnneeId] [int] NULL,
[IndicateurIDDist] [int] NULL,
[ValeurDist] [numeric](5, 2) NULL
) ON [PRIMARY]

I want to show all the TitreIndDist from tblIndicateurDist combine with the value of tblIndicateurXAnneeDist and year from tblAnneeTBO. The problem is that tblIndicateurXAnneeDist doesn't always have value.

Here an example that I'm trying to achieve

tblIndicateurDist
1 Test 1
2 Test 2

tblIndicateurXAnneeDist
1 1 55.55

tblAnneeTBO
1 2016 2015-2016 2 1
2 2015 2014-2015 1 1
3 2014 2013-2014 0 0

The result i want

Test 1 2016 55.55
Test 1 2015 null
Test 2 2015 null
Test 2 2016 null

Is that possible?
Here what i try :

SELECT tbo.tblIndicateurDist.TitreIndDist, tbo.tblAnneeTBO.Annee4Chiffre
FROM tbo.tblAnneeTBO LEFT OUTER JOIN
tbo.tblIndicateurXAnneeDist ON tbo.tblAnneeTBO.AnneeId = tbo.tblIndicateurXAnneeDist.AnneeId RIGHT OUTER JOIN
tbo.tblIndicateurDist ON tbo.tblIndicateurXAnneeDist.IndicateurIDDist = tbo.tblIndicateurDist.IndicateurIDDist

Tks
Luc

2

Ok i fix my problem by inserting blank data when i create a new TitreIndDist in tblIndicateurDist.
Tks

3

Or you could:

with cte
  as (select distinct
             Annee4Chiffre
        from tbo.tblAnneeTBO
     )
select a.TitreIndDist
      ,a.ObjetID
      ,b.Annee4Chiffre
      ,case when d.AnneeId is null then null else c.ValeurDist end as ValeurDist
  from tbo.tblIndicateurDist as a
       cross apply cte as b
       left outer join tbo.tblIndicateurXAnneeDist as c
                    on c.IndicateurIDDist=a.IndicateurIDDist
       left outer join tbo.tblAnneeTBO as d
                    on d.AnneeId=c.AnneeId
                   and d.Annee4Chiffre=b.Annee4Chiffre
 order by a.TitreIndDist
         ,a.ObjetID
         ,b.Annee4Chiffre
;
1 Like
4

Tks it working i never use with cte before!

Tks again