Query help for a newbie (maybe subqueries/joins)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help for a newbie (maybe subqueries/joins)

Author

Topic

xaeryan
Starting Member

2 Posts

Posted - 2008-08-14 : 00:40:31

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 01:10:53

are you using SQL 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 01:10:56

[code]SELECT n.DESCRIPTION,c.DESCRIPTION , c.CREATED,c.USER,sc.DESCRIPTION
FROM News n
INNER JOIN Sub_Category sc
ON sc.PID=n.ID
INNER JOIN COMMENTS c
ON c.PID=n.ID[/code]

then use your front end application to join these values to reqd format string.

xaeryan
Starting Member

2 Posts

Posted - 2008-08-14 : 07:48:30

SQL2000 for now, 2008 in the near future.

visakh16: the parent ID (PID) of COMMENTS table is the foreign key to the Sub_Category table, so the last statement:
INNER JOIN COMMENTS c ON c.PID = n.ID should instead be
INNER JOIN COMMENTS c ON c.PID = sc.ID
But this still doesn't get me a single row with the latest comment.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 08:04:22

quote:
Originally posted by xaeryan

SQL2000 for now, 2008 in the near future.

visakh16: the parent ID (PID) of COMMENTS table is the foreign key to the Sub_Category table, so the last statement:
INNER JOIN COMMENTS c ON c.PID = n.ID should instead be
INNER JOIN COMMENTS c ON c.PID = sc.ID
But this still doesn't get me a single row with the latest comment.

so you want single row with latest comment per news?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 08:06:24

[code]DECLARE @News TABLE
(
ID int,
DESCRIPTION varchar(20)
)
INSERT INTO @News
SELECT 1 , 'World News' UNION ALL
SELECT 2 , 'Local News' UNION ALL
SELECT 3 , 'Odd News'

DECLARE @Sub_Category TABLE
(
ID int,
PID int,
DESCRIPTION varchar(20)
)
INSERT INTO @Sub_Category
SELECT 1 , 1 , 'America' UNION ALL
SELECT 2 , 1 , 'Europe' UNION ALL
SELECT 3 , 2 , 'Australia' UNION ALL
SELECT 4 , 2 , 'Asia'

DECLARE @COMMENTS TABLE
(
ID int,
PID int,
DESCRIPTION varchar(20),
CREATED datetime,
[USER] varchar(20)
)
INSERT INTO @COMMENTS
SELECT 1 , 1 , 'Comment 1' , '04/08/2008' , 'SomeUser' UNION ALL
SELECT 2 , 3 , 'Comment 2' , '07/05/2008' , 'MrAnderson' UNION ALL
SELECT 3 , 1 , 'Comment 3' , '01/11/2008' , 'FrogsAreYummy' UNION ALL
SELECT 4 , 2 , 'Comment 4' , '03/15/2008' , 'SQLRules'

-- SQL Server 2000 way
SELECT n.[DESCRIPTION] AS [News],
c.[DESCRIPTION] AS [Comments],
c.CREATED,
c.[USER],
s.[DESCRIPTION] AS [Sub-Category]
FROM @News n
INNER JOIN @Sub_Category s ON n.ID = s.PID
INNER JOIN @COMMENTS c ON s.ID = c.PID
INNER JOIN
(
SELECT n.ID, LATEST = MAX(c.CREATED)
FROM @News n
INNER JOIN @Sub_Category s ON n.ID = s.PID
INNER JOIN @COMMENTS c ON s.ID = c.PID
GROUP BY n.ID
) l ON n.ID = l.ID
AND c.CREATED = l.LATEST

-- SQL Server 2005 & above
SELECT [News],
[Comments],
CREATED,
[USER],
[Sub-Category]
FROM (

SELECT n.[DESCRIPTION] AS [News],
c.[DESCRIPTION] AS [Comments],
c.CREATED,
c.[USER],
s.[DESCRIPTION] AS [Sub-Category],
row_no = row_number() OVER (PARTITION BY n.ID ORDER BY c.CREATED DESC)
FROM @News n
INNER JOIN @Sub_Category s ON n.ID = s.PID
INNER JOIN @COMMENTS c ON s.ID = c.PID
) n
WHERE n.row_no = 1

/* RESULT :
News Comments CREATED USER Sub-Category
-------------------- -------------------- ----------- -------------------- --------------------
World News Comment 1 2008-04-08 SomeUser America
Local News Comment 2 2008-07-05 MrAnderson Australia

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources