December 2, 2009 at 4:36 am
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)idanotherid(No column name)idanotherid
#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)idanotherid(No column name)idanotherid
#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
December 2, 2009 at 5:03 am
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 🙂
December 2, 2009 at 7:59 am
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
Consultant - Straight Path Solutions
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply