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)
 Join on null or a value - one row return only

Author  Topic 

duncanwill
Starting Member

20 Posts
Posted - 2012-02-06 : 08:03:10
Hi,

I have a conundrum that has perplexed me for a while whereby I need to return a pieces of data from rows on the basis that if an explicit value exists return that but if the value does not exist then we need to 'fall back' to a default value which has the value of NULL as the match.

So, a manufacturer may have a range of products and, optionally (for example based on the manufacturers own rules) each product may have a code that identifies it uniquely or perhaps one code 'does all' for this manufacturer.

The JOIN is therefore based on manufacturer and product, but the PRODUCT may be NULL to signify "All products", or alternatively to reflect the "default" or "fall back" code for those products not given a unique code for that manufacturer.

Bear in mind that this is 'reflecting' a real situation (that I do not have control over table structure or data) and I am trying to simplfy for examples!


The following code hopefully highlights the problem fully and what I am after is an elegant solution, if one exists! I am not sure if there is a simple answer (and therefore I am missing the obvious) or if in fact I need to use a derived or temporary table and effectively 'double' my query execution:

The ultimate aim of the code below is to get a list of orders with associated joined data including the PRODUCT_CODE. There should be 4 rows returned - with ORDER_ID 2 only being returned once and the PRODUCT_CODE being "11111".

Code:


-- start standing data tables
CREATE TABLE #Retailer
(
	RETAILER_NAME varchar(20),
	POSTCODE varchar(10)
)

CREATE TABLE #Product
(
	PRODUCT_NAME varchar(20),
	PRODUCT_DESCRIPTION varchar(1000),
	RETAILER_NAME varchar(20)
)

CREATE TABLE #Retailer_ID_Code
(
	RETAILER_NAME varchar(20),
	PRODUCT_NAME varchar(20),
	PRODUCT_CODE INT
)

INSERT INTO #Product VALUES('Shirt', 'Big Baggy Blue','Smiths')
INSERT INTO #Product VALUES('Coat', 'Wild White Wool','Smiths')
INSERT INTO #Product VALUES('Jumper', 'Rediculous Red','Smiths')
INSERT INTO #Product VALUES('Shirt', 'Yellow','Jones')
INSERT INTO #Product VALUES('Coat', 'Green','Jones')
INSERT INTO #Product VALUES('Jumper', 'Purple','Jones')
INSERT INTO #Product VALUES('Shirt', '100% Cotton','Williams')
INSERT INTO #Product VALUES('Coat', 'Wool','Williams')
INSERT INTO #Product VALUES('Jumper', 'Fleece','Williams')

INSERT INTO #Retailer VALUES('Smiths', 'ZZ1 1TT')
INSERT INTO #Retailer VALUES('Jones', 'AA1 1BB')
INSERT INTO #Retailer VALUES('Williams', 'YY1 1CC')

INSERT INTO #Retailer_ID_Code VALUES('Smiths', 'Shirt', 11111)
INSERT INTO #Retailer_ID_Code VALUES('Smiths', NULL, 22222)
INSERT INTO #Retailer_ID_Code VALUES('Jones', 'Jumper', 33333)
INSERT INTO #Retailer_ID_Code VALUES('Jones', 'Coat', 44444)
INSERT INTO #Retailer_ID_Code VALUES('Jones', 'Shirt', 555555)
INSERT INTO #Retailer_ID_Code VALUES('Williams', NULL, 77777)

-- end standing data tables

-- Create orders data
CREATE TABLE #Orders
(
	ORDER_ID int NOT NULL IDENTITY,
	PRODUCT_NAME varchar(20),
	RETAILER_NAME varchar(20),
	COST decimal(18,2)
)

INSERT INTO #Orders VALUES ('Coat', 'Smiths',88.88)
INSERT INTO #Orders VALUES ('Shirt','Smiths',55.99)
INSERT INTO #Orders VALUES ('Coat', 'Jones',122.22)
INSERT INTO #Orders VALUES ('Coat', 'Williams',55)
-- End Create orders data


SELECT
	o.ORDER_ID,
	o.PRODUCT_NAME,
	o.RETAILER_NAME,
	p.PRODUCT_DESCRIPTION,
	r.POSTCODE,
	o.COST,
	rpc.PRODUCT_CODE
FROM
	#Orders o
INNER JOIN
	#Retailer r ON
	r.RETAILER_NAME = o.RETAILER_NAME
INNER JOIN 
	#Product p ON
	p.PRODUCT_NAME	 = o.PRODUCT_NAME
	AND	
	p.RETAILER_NAME = r.RETAILER_NAME
INNER JOIN
	#Retailer_ID_Code rpc ON
	rpc.RETAILER_NAME = p.RETAILER_NAME
	AND
	(
		rpc.PRODUCT_NAME = p.PRODUCT_NAME
		OR
		rpc.PRODUCT_NAME IS NULL
	)

DROP TABLE #Orders
DROP TABLE #Retailer
DROP TABLE #Product
DROP TABLE #Retailer_ID_Code



Effectively I want to say: "return rows where the Retailer name and product code match, but if the product code does not match return to me the retailer ID code for the NULL row instead."

The desired output would be therefore:
ORDER_ID PRODUCT_NAME RETAILER_NAME PRODUCT_DESCRIPTION POSTCODE COST PRODUCT_CODE
2 Shirt Smiths Big Baggy Blue ZZ1 1TT 55.99 11111
1 Coat Smiths Wild White Wool ZZ1 1TT 88.88 22222
3 Coat Jones Green AA1 1BB 122.22 44444
4 Coat Williams Wool YY1 1CC 55.00 77777



Apologies if this appears confused... when tackling this I thought it to be quite straight forward, but actually doing it and explaining it seems quite tricky!

Many thanks for any help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-06 : 09:41:07
[code]
SELECT o.Order_ID,o.PRODUCT_NAME,o.RETAILER_NAME,p.PRODUCT_DESCRIPTION,rt.POSTCODE,o.COST,COALESCE(r1.PRODUCT_CODE,rt2.PRODUCT_CODE) AS PRODUCT_CODE
FROM #Orders o
INNER JOIN #Product p
ON p.PRODUCT_NAME = o.PRODUCT_NAME
INNER JOIN #Retailer rt
ON rt.RETAILER_NAME = o.RETAILER_NAME
LEFT JOIN #Retailer_ID_Code r1
ON r.RETAILER_NAME = o.RETAILER_NAME
AND r.PRODUCT_NAME = o.PRODUCT_NAME
LEFT JOIN #Retailer_ID_Code r2
ON r.RETAILER_NAME = o.RETAILER_NAME
AND r.PRODUCT_NAME IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

duncanwill
Starting Member

20 Posts
Posted - 2012-02-06 : 10:28:20
Brilliant (and many thanks for the reply)

I think that does the job and I think it falls in to the "I should have known that" category...albeit there is much that I should "know" but i don't!

For completeness sake I had to rejig the aliases and change the join slightly... but I can only take credit for collating your answer... the resuling code for my original example is:




SELECT
o.Order_ID,o.PRODUCT_NAME,o.RETAILER_NAME,p.PRODUCT_DESCRIPTION,
rt.POSTCODE,o.COST,
COALESCE(r1.PRODUCT_CODE,r2.PRODUCT_CODE) AS PRODUCT_CODE ,
FROM #Orders o
INNER JOIN #Product p
ON p.PRODUCT_NAME = o.PRODUCT_NAME

INNER JOIN #Retailer rt
ON rt.RETAILER_NAME = o.RETAILER_NAME
AND
p.RETAILER_NAME = rt.RETAILER_NAME

LEFT JOIN #Retailer_ID_Code r1
ON r1.RETAILER_NAME = o.RETAILER_NAME
AND r1.PRODUCT_NAME = o.PRODUCT_NAME

LEFT JOIN #Retailer_ID_Code r2 ON
r2.RETAILER_NAME = o.RETAILER_NAME
AND r2.PRODUCT_NAME IS NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-06 : 11:51:51
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site