T-SQL Challenge #1

• Ohk Thanks for acknowledge this,,, i write this to solve in SQL 2000 and only for that same data i generate row numbers in this so i write this if you some solution which works on all the data then what i write..

-- Code for Challenge #1

SELECT T1.code, T1.aname, D.bname, D.cname

FROM TableA T1

LEFT JOIN

(

SELECT COALESCE(D2.code, D3.code) AS code

,D2.bname

,D3.cname

FROM

(

SELECT ROW_NUMBER() OVER (PARTITION BY T2.code ORDER BY T2.bname) AS RowID

,T2.*

FROM TableB T2

) D2

FULL JOIN

(

SELECT ROW_NUMBER() OVER (PARTITION BY T3.code ORDER BY T3.cname) AS RowID

,T3.*

FROM TableC T3

) D3

ON D2.code = D3.code

AND D2.RowId = D3.RowId

) D

ON T1.code = D.code

• Sharon Matyk (9/14/2009)

Whilst I enjoyed the mental exercise and learning something I didn't know about 2005, I just can't picture when I would actually do such a join in real life. Is there a logical, meaningful example that was the basis of this problem ... and if not, does anyone have any ideas of when you'd prefer results which only join multiple values in this manner?

Heh... I don't know about now, but I went through that with customer placement with AT&T years ago... even though a 3.5" diskette could hold thousands of new customer records, they only allowed us to put 100 on a disk and they had to all be in the same "region". Even when they shifted to FTP uploads, they still kept the paradigm of only 100 customers in a batch.

The reason why they did this is they would have a keypunch person verify each phone number manually then prep a work order for a tech. They used 100 as the breaking point for how many a tech should be able to handle in a day.

I think they're a little more efficient now... well... maybe. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• WITH dd(code, aname, bname, ROWNUMBER) as

(

select a.code, a.aname, b.bname, ROW_NUMBER() OVER(PARTITION BY a.code order BY a.code )

from @a as a

left outer join @b-2 as b on a.code=b.code

),

ddd(code, cname, ROWNUMBER) as

(

select c.code, c.cname, ROW_NUMBER() OVER(PARTITION BY code order BY code )

from @C as c

)

select COALESCE(d.code, d1.code) as code, COALESCE(aname,(select top 1 a.aname from @a a where a.code=code)) as aname, d.bname, d1.cname

from dd as d

full join ddd as d1 on d.code=d1.code and d.ROWNUMBER=d1.ROWNUMBER;

with d(name,code,ROW)

as(

SELECT bname,code,ROW_NUMBER() OVER(PARTITION BY code order by code) AS row

FROM @b-2 b

),

dd(name,code,ROW) as

(SELECT cname,code,ROW_NUMBER() OVER(PARTITION BY code order by code) AS row

FROM @C

)

SELECT a.* , b.name1, b.name2

FROM @a a

LEFT JOIN (

select d.code as code1, d.name as name1, dd.code as code2, dd.name as name2

from d

FULL JOIN dd ON d.code = dd.code AND d.row = dd.row ) b

ON a.code IN (b.code1, b.code2)

• Ouch! I just realized that I triple-posted the other day when I was having a lot of trouble with the site... :w00t:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc.
[/font]
[font="Verdana"] "Performance is our middle name."[/font]

• First we need to full join the tables @b-2 and @C based on the code and the position of the bname and cname. Then later make right join with @a table.

select a1.code,a1.aname,b1.bname,c1.cname

from @C c1

full join @b-2 b1

on c1.code=b1.code and

(select count(c2.code) from @C c2 where c1.cname>=c2.cname and c1.code=c2.code)

=

(select count(b2.code) from @b-2 b2 where b1.bname>=b2.bname and b1.code=b2.code)

right join @a a1 on isnull(b1.code,c1.code)= a1.code

• select

a.code

, aname

, bname

, cname

from (

select

code

, bName

, ROW_NUMBER() over (partition by code order by code) as br

from @b-2

) b

right join (

select

code

, cname

, ROW_NUMBER() over (partition by code order by code) as cr

from @C

) c on b.code = c.code and b.br = c.cr

right join @a a on c.code = a.code

• Here is what I came up with:

SELECT ISNULL(ac.code, b1.code) AS code, a2.aname, b1.bname, ac.cname

FROM (SELECT a.code, a.aname, c.cname, ROW_NUMBER() OVER (Partition BY a.aname ORDER BY a.code, a.aname) AS RNO

FROM @a AS a

LEFT JOIN @C AS c

ON a.code = c.code) AS ac

FULL JOIN (SELECT b.code, b.bname, ROW_NUMBER() OVER (Partition BY b.code ORDER BY b.code, b.bname) AS RNO

FROM @b-2 b) AS b1

ON ac.code = b1.code

AND ac.RNO = b1.RNO

LEFT JOIN @a AS a2

ON ISNULL(ac.code, b1.code) = a2.code

ORDER BY ISNULL(ac.code, b1.code), a2.aname, b1.bname, ac.cname

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

• select a.*,d.bname,d.cname from @a a

left join

(select b.code b_code,b.bname,c.code c_code,c.cname from

(select ROW_NUMBER() over(partition by code order by code)Rank

,* from @b-2)b full join

(select ROW_NUMBER() over(partition by code order by code)Rank

,* from @C)c

on b.code=c.code and b.rank=c.rank)d

on a.code in (b_code,c_code)

Viewing 8 posts - 16 through 22 (of 22 total)