JOINS with COALESCE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » JOINS with COALESCE

JOINS with COALESCE

Rate Topic

Display Mode

Topic Options

Author

Message

deepakkn

Posted Wednesday, December 02, 2009 4:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 27, 2010 2:33 AM
Points: 186, Visits: 238

Hi All,

Please fine the following is the scenario:

CREATE TABLE #ABC
(
id INT IDENTITY(1,1),
anotherid INT
)

CREATE TABLE #DEF
(
id INT IDENTITY(1,1),
anotherid INT
)

INSERT INTO #ABC
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10

INSERT INTO #DEF
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 10

SELECT '#ABC',A.*,'#DEF',B.* FROM #ABC A
LEFT JOIN #DEF B ON A.anotherid = COALESCE(B.anotherid,B.anotherid+1,B.anotherid+2)

Please check the following OUTPUT:

(No column name) id anotherid (No column name) id anotherid
#ABC 1 1 #DEF 1 1
#ABC 2 2 #DEF 2 2
#ABC 3 3 #DEF NULL NULL
#ABC 4 4 #DEF NULL NULL
#ABC 5 5 #DEF NULL NULL
#ABC 6 6 #DEF 3 6
#ABC 7 7 #DEF 4 7
#ABC 8 8 #DEF 5 8
#ABC 9 9 #DEF NULL NULL
#ABC 10 10 #DEF 6 10

I need to output like this:
Instead of NULL i need the next number in the another id column.(6)

(No column name) id anotherid (No column name) id anotherid
#ABC 1 1 #DEF 1 1
#ABC 2 2 #DEF 2 2
#ABC 3 3 #DEF 3 6
#ABC 4 4 #DEF 3 6
#ABC 5 5 #DEF 3 6
#ABC 6 6 #DEF 3 6
#ABC 7 7 #DEF 4 7
#ABC 8 8 #DEF 5 8
#ABC 9 9 #DEF 7 10
#ABC 10 10 #DEF 7 10

Please let me know if this is not understandable!!

Thanks

Deepak

Post #827319

Simon Liddle

Posted Wednesday, December 02, 2009 5:03 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:41 AM
Points: 923, Visits: 2,942

Based on your sample data, this would work, but it relies on #DEF.anotherid being unique and inserted into the table in numerical order - it's not a particularly nice piece of T-SQL!

SELECT	
	'#ABC',
	A.*,
	'#DEF',
	MIN(b.id) id,
	MIN(b.anotherid) anotherid FROM #ABC A
LEFT OUTER JOIN #DEF B ON A.anotherid <= B.[anotherid]
GROUP BY a.id, a.[anotherid]

If you didn't need to see #DEF.id in your results then you could drop the column from your select list and this code should work regardless of the order #DEF.anotherid is inserted in.

Just out of curiosity, what is it you are trying to do?

Cheers,
Simon Smile

Post #827333

Jack Corbett

Posted Wednesday, December 02, 2009 7:59 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Your issue is that, in the ON clause B.anotherid is never null so you are never getting to anotherid + 1 or anotherid + 2 as the evaluation is only returning a true or false.

Simon's solution is good.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #827495

« Prev Topic | Next Topic »

Permissions