mutliple join criteria which may be NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

mutliple join criteria which may be NULL

Author

Topic

obiron
Starting Member

23 Posts

Posted - 2009-05-27 : 07:04:31

Hi guys,

This could be a tricky one and I am not even sure whether it is possible.

Lets say I have a sales analysis table, a budgets table and a sales-budgets mapping table.

Sales analysis holds
Saleman ID
Salesman Team
Product ID
Product Group
Customer ID
Customer Classification
(there would be other data for summing and aggregating - order ID, Order Line ID, Qty, price, etc..)

The Budget table holds
Budget Line #
Budget code
Budget value

The Cross Reference table holds
Sales Team ID
Product Group
Customer Classification
Budget Line #

Lets say that sales teams are North, South, East, West, Midlands
Lets say that Product Groups are Bearings, Shafts, Pumps and anything else gets lumped in together
Lets stay that Customer classes are Gold,Silver,Bronze

For Pumps the budgets are set by Team and Customer classes
For Shafts, Budgets are set by Customer Classes
For Bearings, Budgets are set by Team

the X-ref Table Looks like this


Team        Product     Cust      BudgetLine#
North       Pumps       Gold      1
South       Pupms       Gold      2
East        Pumps       Gold      3
West        Pumps       Gold      4
Mids        Pumps       Gold      5
North       Pumps       Silver    6
South       Pupms       Silver    7
East        Pumps       Silver    8
West        Pumps       Silver    9
Mids        Pumps       Silver    10
North       Pumps       Bronze    11
South       Pupms       Bronze    12
East        Pumps       Bronze    13
West        Pumps       Bronze    14
Mids        Pumps       Bronze    15
NULL        Shafts      Gold      16
NULL        Shafts      Silver    17
NULL        Shafts      Bronze    18
North       Bearings    NULL      19
South       Bearings    NULL      20
East        Bearings    NULL      21
West        Bearings    NULL      22
Mids        Bearings    NULL      23
NULL        NULL        NULL      24 // captures all other sales

I want to create a SELECT statement that joins the Sales Analysis to the X-ref table to give me the budget line number so that I can then join to the budgets table.

Has anyone else had to do this where the values are potentially NULL in your right hand table and you have multiple join values.

Current plan is to do



SELECT   S.*
         X.BudgetLineNumber,
         B.BudgetCode
FROM   
         Sales S
  JOIN
         X_ref X ON
            S.Team = coalesce(X.Team,S.Team)
         AND
            S.ProdGroup = coalesce(X.ProdGroup,S.Prodgroup)
         AND
            S.CustType = coalesce(X.CustType,S.CustType)
   JOIN
         Budgets B ON
            B.BudgetLineNumber = X.BudgetLineNumber

What happens if there are multiple matching records when NULLs are applied (e.g. Customer has been classified as 'TIN' and product has been classified as 'FLANGE' and Team has been identified as 'SPECIAL'
and the X-ref lines exist

NULL,NULL,TIN,32
NULL,FLANGE,NULL,35
SPECIAL,FLANGE,NULL,37
SPECIAL,NULL,NULL,38

All of these lines will match the selection criteria. Does it then matter which order the join conditions are presented or will all lines match regardless.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 07:18:56

All lines will match.
You have to decide which business rule to apply for only returning one record of those 4 above.
Which combinations, hits, are more worth?

E 12°55'05.63"
N 56°04'39.26"

obiron
Starting Member

23 Posts

Posted - 2009-05-27 : 08:42:36

So I will need to
Add a ranking factor to the X_ref table,
rewrite it as a sub query ordered by ranking factor
Select TOP 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 08:45:39

Or use the built-in ROW_NUMBER() function to get the highest ranked record.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 09:59:02

according to you, what should you be getting out of above four rows?

obiron
Starting Member

23 Posts

Posted - 2009-05-28 : 08:07:51

Thats the $64000 question.

I know I only want to return 1 match in the X_ref table for each row in the sales table but I don't know which one, and it may be different rankings for different sales combinations.

I am probably safe to say that I want to return an exact match first, then a match with only 1 NULL, then a match with 2 NULLs etc..
But it is how you decide which NULL takes precedence.

The example given above is theoretical, the production solution will have at least 8 and possibly 12 elements to the X_ref table.

I could have another table that ranks the elements that is then used to build the order by clause in the SQL and call it using sp_executeSQL() but that is starting to get nasty :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 08:31:27

This is one way. I also took the liberty to make the three different columns Team, Product and Cust weighted.
It means in this suggstion that a match on Team column is worth more, it has a weight of 4 and a match on Product column has a weight of 2.

DECLARE	@CrossRef TABLE
	(
		Team VARCHAR(20),
		Product VARCHAR(20),
		Cust VARCHAR(20),
		BudgetLine INT,
		HitWeight AS COALESCE(4 * SIGN(LEN(Team)), 0) + COALESCE(2 * SIGN(LEN(Product)), 0) + COALESCE(1 * SIGN(LEN(Cust)), 0)
	)

INSERT	@CrossRef
SELECT	'North', 'Pumps', 'Gold', 1 UNION ALL
SELECT	'South', 'Pumps', 'Gold', 2 UNION ALL
SELECT	'East', 'Pumps', 'Gold', 3 UNION ALL
SELECT	'West', 'Pumps', 'Gold', 4 UNION ALL
SELECT	'Mids', 'Pumps', 'Gold', 5 UNION ALL
SELECT	'North', 'Pumps', 'Silver', 6 UNION ALL
SELECT	'South', 'Pumps', 'Silver', 7 UNION ALL
SELECT	'East', 'Pumps', 'Silver', 8 UNION ALL
SELECT	'West', 'Pumps', 'Silver', 9 UNION ALL
SELECT	'Mids', 'Pumps', 'Silver', 10 UNION ALL
SELECT	'North', 'Pumps', 'Bronze', 11 UNION ALL
SELECT	'South', 'Pumps', 'Bronze', 12 UNION ALL
SELECT	'East', 'Pumps', 'Bronze', 13 UNION ALL
SELECT	'West', 'Pumps', 'Bronze', 14 UNION ALL
SELECT	'Mids', 'Pumps', 'Bronze', 15 UNION ALL
SELECT	NULL, 'Shafts', 'Gold', 16 UNION ALL
SELECT	NULL, 'Shafts', 'Silver', 17 UNION ALL
SELECT	NULL, 'Shafts', 'Bronze', 18 UNION ALL
SELECT	'North', 'Bearings', NULL, 19 UNION ALL
SELECT	'South', 'Bearings', NULL, 20 UNION ALL
SELECT	'East', 'Bearings', NULL, 21 UNION ALL
SELECT	'West', 'Bearings', NULL, 22 UNION ALL
SELECT	'Mids', 'Bearings', NULL, 23 UNION ALL
SELECT	NULL, NULL, NULL, 24

DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1),
		Team VARCHAR(20),
		Product VARCHAR(20),
		Cust VARCHAR(20)
	)

INSERT	@Sample
SELECT	'North', 'Pumps', 'Gold' UNION ALL
SELECT	'North', 'Pumps', 'Silver'

SELECT		*
FROM		(
			SELECT		s.*,
					cr.BudgetLine,
					ROW_NUMBER() OVER (PARTITION BY s.RowID ORDER BY cr.HitWeight DESC) AS recID
			FROM		@Sample AS s
			INNER JOIN	@CrossRef AS cr ON COALESCE(cr.Team, s.Team) = s.Team
						AND COALESCE(cr.Product, s.Product) = s.Product
						AND COALESCE(cr.Cust, s.Cust) = s.Cust
		) AS d
WHERE		recID = 1
ORDER BY	RowID

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources