Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

JOINS with COALESCE Expand / Collapse
Author
Message
Posted Wednesday, December 2, 2009 4:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, December 2, 2009 5:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 1,114, Visits: 3,126
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
Post #827333
Posted Wednesday, December 2, 2009 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 10,196, Visits: 13,126
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 »

Add to briefcase

Permissions Expand / Collapse