Sql-server – SQL Join to a Same Combination of Tables

join;sql serversql-server-2016

I want to join a first table to same combination of joins: X,Y,Z. The first table can be different table types, but they all join to same set of tables. Table A may have 8 columns of different data types, Table B may have 15 columns of different table types. How do I code for this without repetition? The tables join to other tables later. How would I save this combinations of joins in a view or table function, etc, if the first table is always different?

We have 100+ queries like this, and eventually may want to add more tables to saved X-Y-Z combination, maybe 7 in the future. S-T-U-V-X-Y-Z. The question becomes more of code versatility. Would like to change in 1 spot, rather than 100 different queries.

Note: Table X,Y,Z have different columns and data types, except on ProductId and ProductType.

Select a.*, x.*,y.*,z.* 
from TableA a
left join DimShelf x
  on b.ProductId = x.ProductId 
  and b.ProductTypeId = x.ProductTypeId -- 1
left join DimDesk y
  on b.ProductId = y.ProductId 
  and b.ProductTypeId = y.ProductTypeId -- 2
left join DimCouch
  on b.ProductId = z.ProductId 
  and b.ProductTypeId = z.ProductTypeId -- 3
left join other items of variation with where clauses at end...

Select b.*, x.*,y.*,z.* 
from Tableb b
left join DimShelf x
  on b.ProductId = x.ProductId 
  and b.ProductTypeId = x.ProductTypeId -- 1
left join DimDesk y
  on b.ProductId = y.ProductId 
  and b.ProductTypeId = y.ProductTypeId -- 2
left join DimCouch
  on b.ProductId = z.ProductId 
  and b.ProductTypeId = z.ProductTypeId -- 3
left join other items of variation with where clauses at end...




create table dbo.DimChair
(
    DimChairId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    Color varchar(55),
    LegNumber int
)

create table dbo.DimShelf
(
    DimShelfId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    Length float,
    Height float,
    NumberofShelves varchar(55)
)

create table dbo.DimCouch
(
    DimCouchId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    FabricType varchar(255),
)

Best Answer

As suggested by @Akina, it might be better to just write a query that joins the desired table to a view that joins the common tables. In this manner, we might be able to get good optimization and inlining of the view.

We need to note that for all 3 joins, we use the same value from the table joining to each table. Therefore we should be able to create a view that has the entire set of value {ProductID, ProductType} available to join.

I will assume that not all valid values necessarily exist in each table, so we can't just left join the other tables to the X table; it might not even have that value that we ultimately are looking for. Thus, we need a set of all possible values in all tables first.

CREATE VIEW AllThings AS
WITH AllPossibleValues AS (
  SELECT ProductID, ProductType
  FROM DimShelf 
  UNION 
  SELECT ProductID, ProductType
  FROM DimDesk
  UNION 
  SELECT ProductID, ProductType
  FROM DimCouch
)
SELECT 
  b.ProductID,
  b.ProductType,
  x.... -- All columns you need from x
  y.... -- All columns you need from y
  x.... -- All columns you need from z
FROM AllPossibleValues AS b
LEFT JOIN DimShelf x
  ON b.ProductId = x.ProductId 
  AND b.ProductTypeId = x.ProductTypeId -- 1
LEFT JOIN DimDesk y
  ON b.ProductId = y.ProductId 
  AND b.ProductTypeId = y.ProductTypeId -- 2
LEFT JOIN DimCouch
  on b.ProductId = z.ProductId 
  and b.ProductTypeId = z.ProductTypeId -- 3
;

Note that it is not recommended to use SELECT * with any of the table in a view definition - it implies that it'll select all columns but in fact it only selects all columns that existed at the time of view's creation/modification. For that reason, it's best to enumerate the columns explicitly. This does make for lengthy query definition but as @Akina correctly points out, that will not necessarily hurt the optimization.

Furthermore, if you find the performance to be problematic, you might need to push down the predicate to the CTE, which necessitates the use of table-valued function in order to pass a parameter. Something similar to this:

CREATE FUNCTION AllThingsParameterized (
  @ProductID int,
  @ProductType varchar(50)
) AS RETURNS TABLE
RETURN
WITH AllPossibleValues AS (
  SELECT ProductId, ProductType
  FROM DimCouch AS x
  WHERE ProductID = @ProductID
    AND ProductType = @ProductType
  UNION
  ....
) ...

This variant, however, is good only for one value of the ProductID & ProductType; if you need a set of ProductID & ProductType, then you may need to consider using a table-valued parameter, which may further complicate the setup.

Do note that if the view is used in another view, you may negate the benefit of inlining as the final statement may become too complex for the optimizer to effectively optimize. If that is your typical scenario, you might be actually better off generating a temporary table result from a stored procedure and joining the table to that instead.

Related Question