Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


Table: Customer


Table: Discounts


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


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:


My query currently looks like this:

SELECT Art_number, Art_description, Alt_Art_Nr, Cust_name
FROM Articles,
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?

share|improve this question
Your 4th snippet ("should look like this") shows in the first line a row that I can't make sense of. How did the fork article become linked to the Smith record? – Paul Feb 18 '14 at 15:55
@Paul, thanks for your reply. I see I have simplified my questions to far. The Customer table is also linked to the Article table with another table that contains information about the prices for each article in relation to each customer. So the information is linked via that table also. However the price table contains a reference for all article numbers (for each customer) and didn't gave me a headache like this issue :-) – Herbiek Feb 18 '14 at 16:12
Well, add that 4th table in your question! You don't suppose others have a crystal ball? – ypercubeᵀᴹ Feb 18 '14 at 16:17
@ypercube, Sorry I didn't realize I simplified my example too far. I have added information of the 4th table. Hope it makes sense now. – Herbiek Feb 18 '14 at 20:16
I've noticed there was a answer explaining a left join, but cant find it anymore? – Herbiek Feb 18 '14 at 20:17
up vote 2 down vote accepted

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

      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.):

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

So, the final query should be similar to either:

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


    a.Art_number, a.Art_description, ref.Alt_Art_Nr, c.Cust_name
    Articles AS a
    Discount AS d ON a.Productgroup = d.Productgroup
    Customers AS c ON c.Cust_number = d.Cust_number
    Cross_Ref AS ref
      ON  a.Art_number = ref.Art_number 
      AND c.Cust_number = ref.Customer_Nr ;
share|improve this answer
Thanks ypercube, the second query brought me to the correct solution, especially the AND in the query was an eyeopener. As I see the solution, it looks simple and wondered why I couldn't find this solution myself :S – Herbiek Feb 20 '14 at 11:16

You're probably looking at needing an OUTER JOIN instead of the implied INNER JOINs that you're using. e.g. suppose

   Table 1 Employee (Empl_number, Empl_Name)
   001, Paul
   002, John
   003, Fred

   Table 2 EmployeeRoles (Role_ID, Role_Desc)
   100, Manager
   200, Clerk
   300, KeyHolder

   Table 3 RoleLink (ID, Empl_number, Role_ID
   1, 001, 100
   2, 001, 300
   3, 002, 200

Then to get a list of all employees and their roles you would use

   SELECT e.empl_number, e.empl_name, ISNULL(r.role_desc, 'No role')
   LEFT OUTER JOIN ROLELINK l on e.empl_number = l.empl_number
   LEFT OUTER JOIN EMPLOYEEROLES r on l.role_id = r.role_id 

Would give

   001, Paul, Manager
   001, Paul, Keyholder
   002, John, No role
   003, Fred, Clerk
share|improve this answer
Thanks for you explanation, however after editing my initial question, I now realize that my issue involves 4 tables that all are connected. Only 1 table (Cross_Ref) doesn't have a full list of items so I have to work with LEFT OUTER JOIN, otherwise I end up with only those items related with the items in that table. However I still cant get my head around a correct query describing the relation with JOIN with 4 tables (in a circle?). Can you give me such an example? – Herbiek Feb 19 '14 at 12:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.