SELECT a.Code, a.aname, bc.bname, bc.cname FROM @a a LEFT OUTER JOIN ( SELECT b.bname , c.cname, b.RowN, c.RowN AS cRowN, CASE WHEN b.Code IS NOT NULL THEN b.Code ELSE c.Code END as Code FROM ( SELECT * , ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS RowN FROM @b b) b FULL OUTER JOIN ( SELECT * , ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS RowN FROM @c c) c ON b.Code = c.Code AND b.RowN = c.RowN ) bc On a.Code = bc.Code
kwangsa, Thank you for submitting your entry. The tricky part of this query is to make it work in SQL Server 2000. Your query uses ROW_NUMBER() that is available only in SQL Server 2005.
DECLARE @q1b AS INT DECLARE @q1c AS INT DECLARE @q2 AS VARCHAR(10) DECLARE @q3 AS VARCHAR(10) DECLARE @q4 AS VARCHAR(10)
SET @q1b = (SELECT TOP 1 a.code FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname)) SET @q1c = (SELECT TOP 1 a.code FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname))
WHILE @q1b IS NOT NULL AND @q1c IS NOT NULL BEGIN
SET @q1b = (SELECT TOP 1 a.code FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname)) SET @q1c = (SELECT TOP 1 a.code FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname))
IF (@q1b IS NOT NULL AND @q1b < @q1c) OR @q1c IS NULL INSERT INTO @d SELECT TOP 1 @q1b,a.aname,b.bname,NULL FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname) ELSE IF @q1b IS NULL OR (@q1c IS NOT NULL AND @q1c < @q1b) INSERT INTO @d SELECT TOP 1 @q1c,a.aname,NULL,c.cname FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname) ELSE BEGIN SET @q2 = (SELECT TOP 1 a.aname FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname)) SET @q3 = (SELECT TOP 1 b.bname FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname)) SET @q4 = (SELECT TOP 1 c.cname FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname)) INSERT INTO @d SELECT @q1c,@q2,@q3,@q4 END END
INSERT INTO @d SELECT a.code,a.aname,NULL,NULL FROM @a AS a WHERE NOT EXISTS (SELECT * FROM @b AS b WHERE b.code = a.code) AND NOT EXISTS (SELECT * FROM @c AS c WHERE c.code = a.code)
Posts
My answer :
ReplyDeleteSELECT a.Code, a.aname, bc.bname, bc.cname FROM @a a
LEFT OUTER JOIN
(
SELECT b.bname , c.cname, b.RowN, c.RowN AS cRowN,
CASE WHEN b.Code IS NOT NULL THEN b.Code ELSE c.Code END as Code
FROM ( SELECT * , ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS RowN FROM @b b) b
FULL OUTER JOIN ( SELECT * , ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS RowN FROM @c c) c
ON b.Code = c.Code
AND b.RowN = c.RowN
) bc
On a.Code = bc.Code
kwangsa,
ReplyDeleteThank you for submitting your entry. The tricky part of this query is to make it work in SQL Server 2000. Your query uses ROW_NUMBER() that is available only in SQL Server 2005.
I will post the next challenge in a day or two.
regards
Jacob
I'm sure there's a far shorter way of doing it, but this only uses standard SQL :
ReplyDelete----------------------------------
DECLARE @d TABLE (code INT, aname VARCHAR(10), bname VARCHAR(10), cname VARCHAR(10))
DECLARE @q1b AS INT
DECLARE @q1c AS INT
DECLARE @q2 AS VARCHAR(10)
DECLARE @q3 AS VARCHAR(10)
DECLARE @q4 AS VARCHAR(10)
SET @q1b = (SELECT TOP 1 a.code FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname))
SET @q1c = (SELECT TOP 1 a.code FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname))
WHILE @q1b IS NOT NULL AND @q1c IS NOT NULL
BEGIN
SET @q1b = (SELECT TOP 1 a.code FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname))
SET @q1c = (SELECT TOP 1 a.code FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname))
IF (@q1b IS NOT NULL AND @q1b < @q1c) OR @q1c IS NULL
INSERT INTO @d
SELECT TOP 1 @q1b,a.aname,b.bname,NULL
FROM @a AS a, @b AS b
WHERE a.code = b.code
AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname)
ELSE
IF @q1b IS NULL OR (@q1c IS NOT NULL AND @q1c < @q1b)
INSERT INTO @d
SELECT TOP 1 @q1c,a.aname,NULL,c.cname
FROM @a AS a, @c AS c
WHERE a.code = c.code
AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname)
ELSE
BEGIN
SET @q2 = (SELECT TOP 1 a.aname FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname))
SET @q3 = (SELECT TOP 1 b.bname FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname))
SET @q4 = (SELECT TOP 1 c.cname FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname))
INSERT INTO @d SELECT @q1c,@q2,@q3,@q4
END
END
INSERT INTO @d
SELECT a.code,a.aname,NULL,NULL
FROM @a AS a
WHERE NOT EXISTS (SELECT * FROM @b AS b WHERE b.code = a.code)
AND NOT EXISTS (SELECT * FROM @c AS c WHERE c.code = a.code)
SELECT * FROM @d