SQL Join to a Same Combination of Tables

Question

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

score 1 · Answer 1 · answered Dec 19 '18 at 17:00

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.

Mashchax · Answer 2 · 2018-12-11T16:38:17.257

You can create a view with all the left joins, then left join given view to your source table. You can use dynamic SQL to parametrize table source choice.

This part:

left joinTableX x
  on a.ProductId = x.ProductId
  and a.ProductTypeId = x.ProductTypeId -- 1
left join TableY y
  on a.ProductId = y.ProductId 
  and a.ProductTypeId = y.ProductTypeId -- 2
left join TableZ z
  on a.ProductId = z.ProductId
  and a.ProductTypeId = z.ProductTypeId -- 3
left join other items of variation with where clauses at end...

has to be rewritten as a view definition like this:

create view view_you_need as
select
    all relevant columns
    from TableX x
    left join TableY y
      on x.ProductId = y.ProductId 
      and x.ProductTypeId = y.ProductTypeId -- 2
    left join TableZ z
      on x.ProductId = z.ProductId
      and x.ProductTypeId = z.ProductTypeId -- 3
    left join other items of variation with where clauses at end...

Then you can select from the first table (the one which changes) and left join to a view_you_need view. In order to change the source table dynamically, you can use dynamic SQL, like this:

declare @sourcetable varchar(100) = 'TableB';
declare @sql nvarchar(max);

select @sql = 
'select
    all relevant columns
 from ' + @sourcetable + 
' left join view_you_need 
 on etc etc';

exec(@sql);

Sorry but I'm a little busy, hence the pseudo-code. Hope that helps at least a bit. Good luck.

score 0 · Answer 3 · answered Dec 14 '18 at 06:39

Can you use a view that unions that results of the joined tables then you can simply join once to the view to get the combined LEFT JOIN of all associated tables? In this fashion, you simply add tables to your VIEW as your data set expands

CREATE TABLE TableA (ID INT IDENTITY,
    ProductID INT
)
GO
CREATE TABLE TableB (ID INT IDENTITY,
    ProductID INT
)
GO

CREATE TABLE TableX (ProductID INT IDENTITY, Product VARCHAR(255))
GO
CREATE TABLE TableY (ProductID INT IDENTITY, Product VARCHAR(255))
GO
CREATE TABLE TableZ (ProductID INT IDENTITY, Product VARCHAR(255))
GO

INSERT INTO TableX (Product) VALUES ('TableA-ProductX'), ('TableB-ProductX')
INSERT INTO TableY (Product) VALUES ('TableA-ProductY'), ('TableB-ProductY')
INSERT INTO TableZ (Product) VALUES ('TableA-ProductZ'), ('TableB-ProductZ')
GO

INSERT INTO TableA (ProductID) VALUES (1)
INSERT INTO TableB (ProductID) VALUES (2)
GO

CREATE VIEW vw_AllJoins AS
    SELECT 'TableX' AS [Table], ProductID, Product FROM TableX
    UNION
    SELECT 'TableY' AS [Table], ProductID, Product FROM TableY
    UNION
    SELECT 'TableZ' AS [Table], ProductID, Product FROM TableZ
GO

SELECT *
FROM TableA a
LEFT JOIN vw_AllJoins j ON a.ProductID = j.ProductID

SELECT *
FROM TableB b
LEFT JOIN vw_AllJoins j ON b.ProductID = j.ProductID

DROP TABLE TableA
DROP TABLE TableB
DROP TABLE TableX
DROP TABLE TableY
DROP TABLE TableZ
DROP VIEW vw_AllJoins

score 0 · Answer 4 · answered Dec 21 '18 at 06:26

I think your question is not complete. First para is clear to me.But second para is little confusing.

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.

Do you mean to say X-Y-Z is fixed S,T,U,V table is like tableA and TableB ? or is like S,T,U,V like X-Y-Z ?

It is not clear to anybody ?

left join other items of variation with where clauses at end...

Explaining This matter or not?

Your given example is very plain and simple according to my understanding,

declare @Sql varchar(max)=''

declare @basetable varchar(100)='TableA'

set @Sql='Select a.*, x.*,y.*,z.* 
from '+@basetable+' a
left join DimShelf x
on a.ProductId = x.ProductId 
and a.ProductTypeId = x.ProductTypeId -- 1
left join DimDesk y
on a.ProductId = y.ProductId 
and a.ProductTypeId = y.ProductTypeId -- 2
left join DimCouch
on a.ProductId = z.ProductId 
and a.ProductTypeId = z.ProductTypeId'


print @Sql

execute sp_execute @Sql

Give other example where my query will fail ?

SQL Join to a Same Combination of Tables

4 Answers4