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)
001,fork,cutlery 002,knife,cutlery 003,plate,tableware 004,cup,tableware
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)
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?