How would I write a SQL query to bring back all levels of product categorization accommodating unknowns?

Question

I know the question might have not been too clear but I'll try to explain things better:

I'm building a data warehouse and am working on building a Product dimension table for my ROLAP. The dimension will include the Product, Brand, Client.

So in the OLTP there is a Product table which keys off to Brand table which keys off the Client table. During data entry sometimes the Product isn't known but the Brand is known, so there are sometimes NULLS in the ProductID fields.

So with the data warehouse, I'm creating views on the OLTP that will used via SSIS to load up the ROLAP. I have a view that will create a list of all the products with their brands and clients in it. What I need to do is find a way to add results to that view that will accommodate if the Product or Brand is Unknown or NULL.

I tried using cross joins but I was getting rows with brands having incorrect contracts and such.

Product (ID, ProductName, BrandID)
Brand (ID, BrandName, ClientID)
Client (ID, ClientName)

The end result I'm going for is:

    [ProductID]  [ProductName]  [BrandID]  [BrandName]  [ClientID]  [ClientName]
      1           ProductA       1           BrandA        1          ClientA
      2           ProductB       1           BrandA        1          ClientA
      3           ProductC       2           BrandB        1          ClientA
      ....
      -2          Unknown        43          BrandABC      33         ClientXYZ
      -3          Unknown        -2          Unknown       34         ClientABC
      -1          Unknown        -1          Unknown       -1         Unknown

You may ask if the Product is Unknown how would the brand be known? The reason is this system is for customer calls regarding products and sometimes the caller only knows the brand they are calling about.

So anyhow I hope I explained myself well enough and that someone out there has a solution for me. It would be much appreciated!!

Thanks

Can you post some sample data that you currently have? How does the Client table relate to the Product or Brand tables? I see ContractId in the Brand table, but no ClientId. — sgeddes, Jan 29 '13 at 01:14
Sorry, had a typo there. ContractID should have been ClientID. — Joel R., Jan 29 '13 at 01:18
Unfortunately I can't really post any sample data due the nature of the data. I tried to fill in some fake data above to give you the idea, but maybe it's going to be too difficult for someone to answer without giving them all the details. — Joel R., Jan 29 '13 at 01:28
Is this what you're looking for? http://sqlfiddle.com/#!3/f4fa6/3 -- or do you need negative numbers when NULL? — sgeddes, Jan 29 '13 at 01:37
See answer below -- added case for negative product ids and negative brand ids if needed. Best of luck. — sgeddes, Jan 29 '13 at 01:43

score 0 · Answer 1 · edited Jan 29 '13 at 01:27

0

What you need is an outer join. Something like:

SELECT *
FROM Client AS C
LEFT OUTER JOIN Brand AS B
ON C.Id = B.ClientID
LEFT OUTER JOIN Product AS P
ON P.BrandId = B.Id;

That way you will get all clients independent if they have a brand or even a product.

For more information on JOINs check out my "A Join A Day" series.

edited Jan 29 '13 at 01:27

sgeddes

62,311
6
61
83

answered Jan 29 '13 at 01:21

Sebastian Meine

11,260
29
41

Unfortunately LEFT JOINS won't solve my issue the way you are describing. The reason is I'm building a denormalized view of three tables and want to simply add rows to the result set to include where the Product is Unknown but the Brand is known. So if there are 50 brands, I need 50 added records that show Unknown for the Product with the Brand name. – Joel R. Jan 29 '13 at 01:34

score 0 · Accepted Answer · answered Jan 29 '13 at 01:43

To follow up on my comment, here you go -- you need to use the COALESCE function:

SELECT P.Id as ProductId, COALESCE(P.ProductName,'Unknown') ProductName,
   B.Id as BrandId, COALESCE(B.BrandName,'Unknown') BrandName,
   C.Id as ClientId, C.ClientName
FROM Client AS C
   LEFT OUTER JOIN Brand AS B
      ON C.Id = B.ClientId 
   LEFT OUTER JOIN Product AS P
      ON P.BrandId = B.Id;

And the SQL Fiddle.

BTW -- if you need the negative values instead of NULLs, try this:

SELECT COALESCE(ProductId, productrn*-1) as ProductId, 
   ProductName, 
   COALESCE(BrandId, brandrn*-1) as BrandId,
   BrandName,
   ClientId, 
   ClientName
FROM (
SELECT 
   ROW_NUMBER() OVER(ORDER BY  P.Id) productrn, 
   ROW_NUMBER() OVER(ORDER BY  B.Id) brandrn, 
   P.Id as ProductId, COALESCE(P.ProductName,'Unknown') ProductName,
   B.Id as BrandId, COALESCE(B.BrandName,'Unknown') BrandName,
   C.Id as ClientId, C.ClientName
FROM Client AS C
   LEFT OUTER JOIN Brand AS B
      ON C.Id = B.ClientId 
   LEFT OUTER JOIN Product AS P
      ON P.BrandId = B.Id
  ) t;

And more fiddle.

Good luck.

How would I write a SQL query to bring back all levels of product categorization accommodating unknowns?

2 Answers2