Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 JOINS with COALESCE Rate Topic Display Mode Topic Options
Author
 Message
 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 #ABCSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 UNION ALLSELECT 6 UNION ALLSELECT 7 UNION ALLSELECT 8 UNION ALLSELECT 9 UNION ALLSELECT 10INSERT INTO #DEFSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 6 UNION ALLSELECT 7 UNION ALLSELECT 8 UNION ALLSELECT 10SELECT '#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 10I 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 10Please let me know if this is not understandable!!ThanksDeepak
Post #827319
 Posted Wednesday, December 02, 2009 5:03 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 05, 2013 5:16 AM Points: 1,010, Visits: 3,035
 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 ALEFT 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 02, 2009 7:59 AM
 SSChampion Group: General Forum Members Last Login: Friday, December 06, 2013 7:24 AM Points: 10,808, Visits: 12,330
 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 CorbettApplications Developer Don't let the good be the enemy of the best. -- Paul FlemingCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance ProblemsCrosstabs and Pivots or How to turn rows into columns Part 1Crosstabs and Pivots or How to turn rows into columns Part 2
Post #827495

 Permissions