Not able to produce table based on conditional select

conditionselect

I would like to achieve something that probably ends up in some kind of conditional select (MS SQL) but I do not know how to.

I have tried to simplified my problem to the description below:

I have 3 tables: 1 containing Articles (Art_number, Art_description and Productgroup), the second with Customers (Cust_number and Cust_name) and a third table containing discount information (Cust_number, Productgroup and Discount_percentage)

Table: Articles

001,fork,cutlery
002,knife,cutlery
003,plate,tableware
004,cup,tableware

Table: Customer

1234,smith
5678,jones

Table: Discounts

1234,cutlery,0.9
1234,tableware,0.8
5678,cutlery,0.75
5678,tableware,0.7

My questions is about the relation with a 4th table that contains some (not all!) alternative (customer related) article numbers named Cross_Ref that links the first two tables by containing a article number and a customernumber (Art_number, Alt_Art_Nr and Customer_Nr)

Table: Cross_Ref

002,1002,1234
002,92002,5678
004,1004,1234
004,92004,5678

I would like to produce a table that shows the article numbers in the first column,
Article description in the second column.
The alternative article numbers in the third, column (if available, otherwise empty) and the Customer name in 4th column

It should look like this with the information above:

001,fork,,smith
001,fork,,jones
002,knife,1002,smith
002,knife,92002,jones
003,plate,,smith
003,plate,,jones
004,cup,1004,smith
004,cup,92004,jones

My query currently looks like this:

SELECT Art_number, Art_description, Alt_Art_Nr, Cust_name
FROM Articles,
Customer,
Cross_Ref
WHERE Articles.Art_number = Cross_Ref.Art_number 
and Customer.Cust_number = Cross_Ref.Customer_Nr
and Customer.Cust_number = Discounts.Cust_number
and Articles.Productgroup = Discounts.Productgroup

This only shows me those lines that indeed have an alternative article number, but do not show the articles that do not have a reference mentioned in the Cross_Ref table.

In principle it is a combination of 4 tables that are connected fully by 3 tables, but the 4th table only contains a number of the articles (that have a alternative number) and thereby resulting in only those articlenumbers in the query result, while I would like to see all the article numbers and empty (NULL) fields, if there is no result.

Can anybody show me an example how to archive this?

Best Answer

Based on the output, I think you need a query of the type:

FROM 
    (Base)
  LEFT JOIN
    Cross_Ref
      ON  (Base columns) = (Cross_Ref columns)

Without some more information for the Discount table, we can't be sure but the (Base) should probably be either (the cartesian product of the two tables):

    Articles CROSS JOIN Customers

or (a join using another intermediate table, i.e.):

    Articles 
    JOIN Discount (ON ...) 
    JOIN Customers (ON ...)

So, the final query should be similar to either:

SELECT 
    a.Art_number, a.Art_description, ref.Alt_Art_Nr, c.Cust_name
FROM
    Articles AS a CROSS JOIN Customers AS c
  LEFT JOIN
    Cross_Ref AS ref
      ON  a.Art_number = ref.Art_number 
      AND c.Cust_number = ref.Customer_Nr ;

or:

SELECT 
    a.Art_number, a.Art_description, ref.Alt_Art_Nr, c.Cust_name
FROM
    Articles AS a
  JOIN 
    Discount AS d ON a.Productgroup = d.Productgroup
  JOIN 
    Customers AS c ON c.Cust_number = d.Cust_number
  LEFT JOIN
    Cross_Ref AS ref
      ON  a.Art_number = ref.Art_number 
      AND c.Cust_number = ref.Customer_Nr ;
Related Question