CODEWITHSUNDEEP
sqlsql-serverjoin
Flib
Flib

Reputation: 175

SQL Server avoid repeat same joins

I´m doing the query below where I´m repeating the same joins multiple times, there is a better way to do it? (SQL Server Azure)

Ex.

    Table: [Customer]
    [Id_Customer] | [CustomerName]
    1             | Tomy
    ...

    Table: [Store]
    [Id_Store] | [StoreName]
    1          | SuperMarket
    2          | BestPrice
    ...
    
    Table: [SalesFrutes]
    [Id_SalesFrutes] | [FruteName] | [Fk_Id_Customer] | [Fk_Id_Store]
    1                | Orange      | 1                | 1
    ...

    Table: [SalesVegetable]
    [Id_SalesVegetable] | [VegetableName] | [Fk_Id_Customer] | [Fk_Id_Store]
    1                   | Pea             | 1                | 2
    ...

Select * From [Customer] as C
left join [SalesFrutes] as SF on SF.[Fk_Id_Customer] = C.[Id_Customer]
left join [SalesVegetable] as SV on SV.[Fk_Id_Customer] = C.[Id_Customer]
left join [Store] as S1 on S1.[Id_Store] = SF.[Fk_Id_Store]
left join [Store] as S2 on S1.[Id_Store] = SV.[Fk_Id_Store]

In my real case, I have many [Sales...] to Join with [Customer] and many other tables similar to [Store] to join to each [Sales...]. So it starts to scale a lot the number on joins repeating. There is a better way to do it?

Bonus question: I do like also to have FruteName, VegetableName, StoreName, and each Food table name under the same column.

The Expected Result is:
[CustomerName] | [FoodName] | [SalesTableName] | [StoreName]
Tomy           | Orange     | SalesFrute       | SuperMarket
Tomy           | Pea        | SalesVegetable   | BestPrice
...

Thank you!!

Upvotes: 0

Views: 58

Answers (1)

Stu
Stu

Reputation: 32614

So based on the information provided, I would have suggested the below, to use a cte to "fix" the data model and make writing your query easier.

Since you say your real-world scenario is different to the info provided it might not work for you, but could still be applicable if you have say 80% shared columns, you can just use placeholder/null values where relevant for unioning the data sets and still minimise the number of joins eg to your store table.

with allSales as (
    select Id_SalesFrutes as Id, FruitName as FoodName, 'Fruit' as SaleType, Fk_Id_customer as Id_customer, Fk_Id_Store as Id_Store
    from SalesFruits
    union all
    select Id_SalesVegetable, VegetableName, 'Vegetable', Fk_Id_customer, Fk_Id_Store
    from SalesVegetable
    union all... etc
)
select c.CustomerName, s.FoodName, s.SaleType, st.StoreName
from Customer c
join allSales s on s.Id_customer=c.Id_customer
join Store st on st.Id_Store=s.Id_Store

Upvotes: 1

Related Questions