Monday, May 6, 2013

tsql - is there a better way for these queries

Try this query, I have also provided DDL and sample data for others that you should provide. Please do that in the future.



USE tempdb;
GO

--cleanup start (you never know)
--IF (OBJECT_ID(N'dbo.article_topics ', N'U') IS NOT NULL)
-- DROP TABLE dbo.article_topics ;
--GO
--IF (OBJECT_ID(N'dbo.articles ', N'U') IS NOT NULL)
-- DROP TABLE dbo.articles;
--GO
--IF (OBJECT_ID(N'dbo.topics', N'U') IS NOT NULL)
-- DROP TABLE dbo.topics;
--GO

--DDL


CREATE TABLE dbo.articles
(
acode int IDENTITY(1, 1) NOT NULL,
aname char(13) NOT NULL,
CONSTRAINT PK_articles PRIMARY KEY (acode)
);
GO

CREATE TABLE dbo.topics
(
tcode int NOT NULL,
tname varchar(15) NOT NULL,
CONSTRAINT PK_topics PRIMARY KEY (tcode)
);
GO

CREATE TABLE dbo.article_topics
(
acode int NOT NULL,
tcode int NOT NULL,
CONSTRAINT PK_article_topics PRIMARY KEY (acode, tcode),
CONSTRAINT FK_article_topics_articles FOREIGN KEY (acode) REFERENCES dbo.articles(acode),
CONSTRAINT FK_article_topics_topics FOREIGN KEY (tcode) REFERENCES dbo.topics(tcode),
);
GO

--Insert sample data
INSERT INTO dbo.topics (tcode, tname)
VALUES (1, 'News'), (2, 'Sports'), (3, 'Reality'), (4, 'Middle-East');

INSERT INTO dbo.articles (aname)
SELECT LEFT(NEWID(), 13) FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS T(a) CROSS JOIN (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS T1(a);

INSERT INTO dbo.article_topics (acode, tcode)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 100 + 1 as acode,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 + 1 as tcode
FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS T(a) CROSS JOIN (VALUES (1), (1), (1), (1)) AS T1(a);

--CASE 1. & CASE 2.
SELECT T.tcode, T.tname, AT.acode, AT.aname
FROM dbo.topics AS T
CROSS APPLY
(
SELECT TOP (2) A.acode, A.aname
FROM dbo.articles AS A
INNER JOIN dbo.article_topics AS AT ON A.acode = AT.acode
WHERE AT.tcode = T.tcode
GROUP BY A.acode, A.aname
HAVING COUNT(*) = 1
) AS AT(acode, aname)
ORDER BY T.tcode, AT.acode;

--cleanup at the end
IF (OBJECT_ID(N'dbo.article_topics ', N'U') IS NOT NULL)
DROP TABLE dbo.article_topics ;
GO
IF (OBJECT_ID(N'dbo.articles ', N'U') IS NOT NULL)
DROP TABLE dbo.articles;
GO
IF (OBJECT_ID(N'dbo.topics', N'U') IS NOT NULL)
DROP TABLE dbo.topics;
GO





if (helpful) then Vote();


No comments:

Post a Comment