How to make this JOIN! - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to make this JOIN!

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-11 : 23:35:25

I have a Post Table which is defined as follows:

CREATE TABLE [dbo].[Posts] (
[PostID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Url] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL ,
[Active] [bit] NOT NULL ,
[NoOfComments] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And I have Threads table which is defined like this:

CREATE TABLE [dbo].[Threads] (
[ThreadID] [int] IDENTITY (1, 1) NOT NULL ,
[PostID] [int] NOT NULL ,
[Title] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Email] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now I want to select the post of the current month and year and also I want to select all the threads(comments) of the current post.

The following query returns me all the posts of the current month and year but I am not sure how to return the number of comments associated with the posts.

DECLARE @CurrentYear int
DECLARE @CurrentMonth int

SET @CurrentYear = YEAR(GETDATE())
SET @CurrentMonth = MONTH(GETDATE())

SELECT PostID, Title, Url, Description, DateCreated FROM Posts
WHERE @CurrentYear = YEAR(DateCreated) AND @CurrentMonth = MONTH(DateCreated) AND
Active = 1

Also is it a better idea if I include a Field called NoOfComments in the Post table?

Any ideas!

Mohammad Azam
www.azamsharp.net

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-11 : 23:53:01

just use inner join for Posts and Threads. And using convert to char(6) saves you 2 variables

select *
from Posts p 
inner join Threads t
    on p.PostID = t.PostID
where convert(char(6), p.DateCreated, 112) = convert(char(6), getdate(), 112)
and   p.Active = 1

quote:
Also is it a better idea if I include a Field called NoOfComments in the Post table?

This is not necessary as you can always do a count(*) on the Threads table for a given PostID to obtain the No Of Comments

-----------------
[KH]
Don't live your life in one day

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-12 : 01:15:59

Hi,

Thanks for the reply. But how do I get the count of threads associated with each post. And I want to return the post even if no threads are associated with it.

Thanks,

Mohammad Azam
www.azamsharp.net

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 01:24:23

quote:
Originally posted by azamsharp

Hi,

Thanks for the reply. But how do I get the count of threads associated with each post. And I want to return the post even if no threads are associated with it.

Thanks,

Mohammad Azam
www.azamsharp.net

use left join with group by
Check the Book OnLine help file for details.

-----------------
[KH]
Don't live your life in one day

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-12 : 01:26:59

Thanks!

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 01:44:49

Also refer this
http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx

Madhivanan

Failing to plan is Planning to fail

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-12 : 01:47:32

Hi,

I came up with this query which works fine except for one problem. I want to select p.Description which is of type Text. If I put it in the SELECT List then I also have to put it in GROUP BY and GROUP BY does not support Text datatype. So how can I select p.Description (Text DataType) in my SELECT List.

SELECT p.PostID, p.Url, p.Title,COUNT(t.ThreadID),p.DateCreated "Comments" FROM Posts p
LEFT OUTER JOIN Threads t ON p.PostID = t.PostID
WHERE
p.Active = 1
AND
CONVERT(CHAR(6), p.DateCreated, 112) = CONVERT(CHAR(6),GETDATE(), 112)
GROUP BY p.PostID,p.Url,p.Title,p.DateCreated

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 01:50:35

Select Convert(varchar(8000),p.Description),......
Group by Convert(varchar(8000),p.Description)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 01:55:29

you can use convert() to convert the Description column to varchar. Note Varchar limit is 8000.

-----------------
[KH]
Don't live your life in one day

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-12 : 01:55:49

Hi Madhivanan,

Yes, it worked but whatif the description is larger then 8000.

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 01:58:25

Use Substring

Select Substring(p.Description,1,8000),Substring(p.Description,8001,16000),......
Group by Substring(p.Description,1,8000),Substring(p.Description,8001,16000),......

Madhivanan

Failing to plan is Planning to fail

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-12-12 : 02:00:39

Thanks!

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources