SQLTeam.com | Weblogs | Forums

SQL join - multiple columns and multiple tables


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
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)

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_Family_Code] as ECB_Datasets_Family_Code
      ,ecbd.[DIM1] DIMENSION1_FROM_ECB_Datasets
	  ,ecbdd.[DIM1] as DIMENSION_TABLE
  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


Hi all

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



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.


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]
('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)'),
('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]


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]
          ('BSI - Balance Sheet Items','BSI','CL_FREQ','CL_AREA','CL_ADJUSTMENT'),


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)


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.


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.


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


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!


Sorry I see I simply select * before the individual selects