Search code examples
sqldatabaset-sqlsql-server-2012

Associate data in a table from several crossed tables

I am using SQL Server 2012.

We have Orders from several companies. Each Order can have one or more Sales Order Line. And the Order Line can have one Article, but it never has more than one Article. However, the same article can appear in several Sales Order Line.

Table Order - FinalCustomerId is a foreign key for Customer

Id   FinalCustomerId  Code
1    2                 X45
2    1                 O30
3    2                 Y74
4    5                 XY0

Table Customer

Id   Name
1    XPTO
2    BOSS
3    TREND
4    XIPS
5    VALLEY

Table Article

Id   Name
1    DVD
2    San Disk
3    CD
4    SPAM

Table OrderLine (ordered by OrderId and then by Number) - OrderId is a foreign key for Order, and ArticleId if FK for Article.

Id   Number   OrderId   ArticleId   
1    10       1             1
3    20       1             1
4    30       1             1
7    40       1             2

6    10       2             2
8    20       2             1

2    10       3             2 
5    20       3             2
9    10       4             4

Note: This table has 3 Orders, and only the third order has one type of article (id 2).

Table CustomerArticleMark - MainArticleId is a FK for Article

Id   FinalCustomerId      MainArticleId   
1           2                    1
2           2                    2
3           1                    3
4           5                    2
5           3                    2
6           5                    4

Aim: For each line of CustomerArticleMark associate the respective Sales Order code that have the article.

Rules:

  1. article in CustomerArticleMark appears in 5 Order Lines in two Orders, so Main Article must appear 2 times and not 5 times;
  2. article in CustomerArticleMark appears in 5 Order Lines in one Order, so Main Article must appear only ONCE and not 5 times;
  3. article in CustomerArticleMark does not appear in any Order Lines , so Main Article should appear but with an empty value for Sales Order code;
  4. article in CustomerArticleMark must be unique for each distinct article in Sales Order Line per Order and per FinalCustomer in Order. There are Orders with the same article but different final customer.

Starting in the last table, the desired result should be this one:

Final Table - desired result.

Id (of CustomerArticleMark)   FinalCustomer      MainArticle     SALES ORDER Code 
 
    1                               BOSS              DVD                 X45
   
    2                               BOSS              SanDisk             Y74
    3                               XPTO              CD      (there is no o. line with article 3)      
    4                               VALLEY            SanDisk  -
    5                               TREND             SanDisk       (no final customer id5 in Order)
    6                               VALLEY            SPAM                XY0

It should appear all these six rows. Only in lines with id 3, 4 and 5 the sales order code must be empty, but the rest must be filled!

I have tried with T-SQL:

SELECT DISTINCT  
                   {CustomerArticleMark}.[Id]
                ,  {Customer}.[Name] AS FinalCustomer
                ,  {Article}.[Name] AS MainArticle
                ,  {Order}.[Code] AS SALES ORDER Code
                
             
                FROM {CustomerArticleMark} 
                LEFT JOIN {Customer} ON {Customer}.[Id] = {CustomerArticleMark}.[FinalCustomerId] 
                LEFT JOIN {Article} ON {Article}.[Id] = {CustomerArticleMark}.[MainArticleId] 
                LEFT JOIN {SalesOrderLine} ON {SalesOrderLine}.[ArticleId] = {Article}.[Id]
                LEFT JOIN {Order} ON {Order}.[Id] = {SalesOrderLine}.[OrderId]
                WHERE {Order}.[FinalCustomerId] = {CustomerArticleMark}.[FinalCustomerId]

I get this:

Id (of CustomerArticleMark)   FinalCustomer      MainArticle     SALES ORDER Code 
 
    1                               BOSS              DVD                 X45
    1                               BOSS              SanDisk*            X45*
    
    2                               BOSS              SanDisk             Y74
    6                               VALLEY            SPAM                XY0

it is not appearing the 3, 4 and 5 but it should appear with sales order code empty.

The difference is marked with an asterisk. How can we get the desired result? Thanks for your attention.

Solution

  • Ok, since SalesOrderLine is directly linked to Order we should INNER JOIN them to treat as a single entity. And then we can join this to the CustomerArticleMark. Please try below ...

    SELECT DISTINCT  
                   {CustomerArticleMark}.[Id]
                ,  {Customer}.[Name] AS FinalCustomer
                ,  {Article}.[Name] AS MainArticle
                ,  {Order}.[Code] AS SALES ORDER Code
                
             
                FROM {CustomerArticleMark} 
                LEFT JOIN ( {SalesOrderLine} INNER JOIN {Order} ON {Order}.[Id] = {SalesOrderLine}.[OrderId]) ON {SalesOrderLine}.[ArticleId] = {CustomerArticleMark}.[MainArticleId]
                AND {Order}.[FinalCustomerId] = {CustomerArticleMark}.[FinalCustomerId]
                LEFT JOIN {Customer} ON {Customer}.[Id] = {CustomerArticleMark}.[FinalCustomerId] 
                LEFT JOIN {Article} ON {Article}.[Id] = {CustomerArticleMark}.[MainArticleId]

    Also, in your desired results I don't think you can have the below as the MainArticleId/FinalCustomerId do not have a matching Orderline/Order -

    Id (of CustomerArticleMark)   FinalCustomer      MainArticle     SALES ORDER Code 

1                               BOSS              DVD                 Y74 
6                               VALLEY            SPAM                XY0

    Or, if you wanted to use a CTE ( I've also used GROUP BY below, you could used DISTICNT)

    ;WITH cte as (
SELECT [SalesOrderLine].[ArticleId] , [Order].[FinalCustomerId] , [Order].[Code]  FROM [SalesOrderLine] INNER JOIN [Order] ON [Order].[Id] = [SalesOrderLine].[OrderId])
SELECT 
                   [CustomerArticleMark].[Id]
                ,  [Customer].[Name] AS FinalCustomer
                ,  [Article].[Name] AS MainArticle
                ,  [cte].[Code] AS [SALES ORDER Code]
                
                FROM [CustomerArticleMark] 
                LEFT JOIN [cte] ON [cte].[ArticleId] = [CustomerArticleMark].[MainArticleId]
                AND [cte].[FinalCustomerId] = [CustomerArticleMark].[FinalCustomerId]
                JOIN [Customer] ON [Customer].[Id] = [CustomerArticleMark].[FinalCustomerId] 
                JOIN [Article] ON [Article].[Id] = [CustomerArticleMark].[MainArticleId] 
GROUP BY             [CustomerArticleMark].[Id]
                ,  [Customer].[Name] 
                ,  [Article].[Name] 
                ,  [cte].[Code]