SQL join - multiple columns and multiple tables

Transact-SQL
1

Hi All

Not even sure how to describe my issue as I am no expert in SQL at all. In short I have three tables
Table 1 - Dataset that has the following columns
Dataset_Family_Code
DIM1
DIM2 etc up to DIM 20 (The dim columns contain the DIM_Code value from a table called DIM_Codelist)

Table 2 - DIM_Codelist_Table
Dim_Codelist (This column contains the names of multiple datasets that can have multiple values in the DIM_Code column - For example - This column might have the value Frequency and then in DIM_Code you could have A, M , W and in DIM Code_Description the values would be Annual, Monthly and Weekly)
Dim_Code
Dim_Code_Description

Table 3 - Dataset_Dimensions
Dataset_Family Code (This column is the link between this table and the Dataset table listed first
DIM1 to DIM 20 the values in these columns are the DIM_Codelist values from table listed second.

So in short I have the dataset table that has a Dataset_Family_Codes and DIM_Codes for each Dimension from 1 to 20

What I need to do is to link the DIM_Code back to the DIM_Codelist_Table but first obtain the DIM_Codelist for each dimension as the same value occurs multiple times in the DIM_Code column so I need to know which DIM_Codelist applies to each DIM_Value of each Dataset_Family_Code and return me all the values in new columns called DIM_Code_Description1 to 20.

Unfortunately I am only able to do one join and then I am stuck!

SELECT ecbd.[Id]
      ,ecbd.[Dataset_Code]
      ,ecbd.[Description]
      ,ecbd.[Extract]
      ,ecbd.[Classification]
      ,ecbd.[Dataset_Family_Code] as ECB_Datasets_Family_Code
      ,ecbd.[DIM1] DIMENSION1_FROM_ECB_Datasets
	  ,ecbdd.Dataset_Family_Code 
	  ,ecbdd.[DIM1] as DIMENSION_TABLE
	  ,ecbdc.DIM_Codelist
	  ,ecbdc.DIM_Code
	  ,ecbdc.DIM_Code_Description
	--,ecbd.[DIM2]
      --,ecbd.[DIM3]
      --,ecbd.[DIM4]
      --,ecbd.[DIM5]
      --,ecbd.[DIM6]
      --,ecbd.[DIM7]
      --,ecbd.[DIM8]
      --,ecbd.[DIM9]
      --,ecbd.[DIM10]
      --,ecbd.[DIM11]
      --,ecbd.[DIM12]
      --,ecbd.[DIM13]
      --,ecbd.[DIM14]
      --,ecbd.[DIM15]
      --,ecbd.[DIM16]
      --,ecbd.[DIM17]
	  
  FROM [dbo].[DIM_ECB_Datasets] ecbd
  inner join [dbo].DIM_ECB_Datasets_Dimensions ecbdd on ecbdd.Dataset_family_code = ecbd.Dataset_Family_Code 
  inner join [dbo].DIM_ECB_Dataset_Dimension_Codelist ecbdc on ecbdc.DIM_Codelist = ecbdd.DIM1 where ecbd.[DIM1] = ecbdc.DIM_Code
2

Hi all

Just wondering if I need to provide anything further as I have not had a response as yet?

Thanks!

3

Yeah, some sample data as CREATE TABLE and INSERT statements would be most helpful, i.e. not a picture of the data or a text listing of data. The description is hard to follow without data.

4

Hi Scott

Here are the create and insert statements. Thanks!

CREATE TABLE [dbo].[DIM_ECB_Dataset_Dimension_Codelist](
	[DIM_Codelist] [varchar](100) NULL,
	[DIM_Code] [varchar](100) NULL,
	[DIM_Code_Description] [varchar](300) NULL
) 


INSERT INTO [dbo].[DIM_ECB_Dataset_Dimension_Codelist]
           ([DIM_Codelist]
           ,[DIM_Code]
           ,[DIM_Code_Description])
     VALUES
('CL_FREQ','A','Annual'),
('CL_FREQ','M','Monthly'),
('CL_FREQ','W','Weekly'),
('CL_AREA','1A','International organisations'),
('CL_AREA','1B','UN organisations'),
('CL_AREA','1C','IMF (International Monetary Fund)'),
('CL_AREA','1D','WTO (World Trade Organisation)'),
('CL_AME_AREA_EE','1A','International organisations'),
('CL_AME_AREA_EE','1B','UN organisations'),
('CL_AME_AREA_EE','1C','International Monetary Fund (IMF)'),
('DIM_Codelist','DIM_Code','DIM_Code_Description'),
('CL_ADJUSTMENT','B','Other methods of seasonal adjustment'),
('CL_ADJUSTMENT','C','Trend-cycle data, working day adjusted, not seasonally adjusted'),
('CL_ADJUSTMENT','K','Calendar factors'),
('CL_ADJUSTMENT','N','Neither seasonally nor working day adjusted'),
('CL_AME_TRANSFORM','1','Original data and moving arithmetic mean'),
('CL_AME_TRANSFORM','3','Index numbers and moving arithmetic mean')






CREATE TABLE [dbo].[DIM_ECB_Datasets](
	[Id] [int] NOT NULL,
	[Dataset_Family_Code] [varchar](10) NULL,
	[DIM1] [varchar](50) NULL,
	[DIM2] [varchar](50) NULL,
	[DIM3] [varchar](50) NULL,
	
) 





INSERT INTO [dbo].[DIM_ECB_Datasets]
           ([Id]
           ,[Dataset_Family_Code]
           ,[DIM1]
           ,[DIM2]
           ,[DIM3]
           )
     VALUES
           ('11390000','BSI','M','MT','N'),
	('11827569','AME','A','AUT','1')

           






CREATE TABLE [dbo].[DIM_ECB_Datasets_Dimensions](
	[Dataset_Code_Short_Description] [varchar](300) NULL,
	[Dataset_Family_Code] [varchar](10) NULL,
	[DIM1] [varchar](50) NULL,
	[DIM2] [varchar](50) NULL,
	[DIM3] [varchar](50) NULL,
) 



INSERT INTO [dbo].[DIM_ECB_Datasets_Dimensions]
           ([Dataset_Code_Short_Description]
           ,[Dataset_Family_Code]
           ,[DIM1]
           ,[DIM2]
           ,[DIM3]
           
     VALUES
          ('BSI - Balance Sheet Items','BSI','CL_FREQ','CL_AREA','CL_ADJUSTMENT'),
	('AME - AMECO','AME','CL_FREQ','CL_AME_AREA_EE','CL_AME_TRANSFORM')
5

Not sure I'm really on target, but try the JOINs below:

FROM [dbo].[DIM_ECB_Datasets] ecbd
INNER JOIN [dbo].DIM_ECB_Datasets_Dimensions ecbdd ON ecbdd.Dataset_family_code = ecbd.Dataset_Family_Code 
INNER JOIN [dbo].DIM_ECB_Dataset_Dimension_Codelist ecbdc ON 
    (ecbdc.DIM_Codelist = ecbdd.DIM1 AND ecbdc.DIM_Code = ecbd.DIM1) OR
    (ecbdc.DIM_Codelist = ecbdd.DIM2 AND ecbdc.DIM_Code = ecbd.DIM2) OR
    (ecbdc.DIM_Codelist = ecbdd.DIM3 AND ecbdc.DIM_Code = ecbd.DIM3)
6

Hi Scott

Thanks for your reply and apologies for my delay. Yes unfortunately that does not work. I would need the result of each join for each DIM value to be returned in a separate column like DIM1_Code_Description and then DIM1_Code_Description. At present it joins on sum of DIM1 and then some on DIM2 and some of DIM3.

7

Sorry - not taken the time to format properly. Do you want it displayed like this?

select dims.dataset_family_code, dims.dims, dims.content, das.content, cod.dim_code_description
from (
select Dataset_family_code, u.dims, u.content
from [DIM_ECB_Datasets_Dimensions]
unpivot (content for dims in (dim1, dim2, dim3)) u
) dims
inner join
(
select Dataset_family_code, u.dims, u.content
from [DIM_ECB_Datasets]
unpivot (content for dims in (dim1, dim2, dim3)) u
) das
ON dims.dataset_family_code = das.dataset_family_code
AND dims.dims = das.dims
inner join [DIM_ECB_Dataset_Dimension_Codelist] cod
ON cod.dim_codelist = dims.content
and cod.dim_code = das.content

I've refrained from doing pivot dynamically, but if your data model changes, you might want to. XML is probably a better option, but you can make that call depending on your data.

8

You could of course leave it using the column layout. There are multiple approaches, the most messy of which (not recommended) is:

select a.dataset_family_code, b.dataset_code_short_description, 
b.dim1, a.dim1, c1.dim_code_description, 
b.dim2, a.dim2, c2.dim_code_description, 
b.dim3, a.dim3, c3.dim_code_description
from [DIM_ECB_Datasets] a
inner join [DIM_ECB_Datasets_Dimensions] b
on a.dataset_family_code = b.dataset_family_code
left outer join [DIM_ECB_Dataset_Dimension_Codelist] c1
on c1.dim_codelist = b.dim1
and c1.dim_code = a.dim1
left outer join [DIM_ECB_Dataset_Dimension_Codelist] c2
on c2.dim_codelist = b.dim2
and c2.dim_code = a.dim2
left outer join [DIM_ECB_Dataset_Dimension_Codelist] c3
on c3.dim_codelist = b.dim3
and c3.dim_code = a.dim3
9

Thanks Simon! That last one is exactly what I want with one small change I would like to see all of this joined to whole DIM_ECB_Dataset table. Is this achievable? Also with the first solution if I wanted to pivot I would I achieve this.

Thanks again!

10

Sorry I see I simply select * before the individual selects