In need of a subquery. - 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 2005 Forums

Transact-SQL (2005)

In need of a subquery.

Author

Topic

tpayne
Starting Member

18 Posts

Posted - 2008-08-13 : 11:15:35

I have 2 tables PitchStatus and PitchStatusTypes.
PitchStatus
CREATE TABLE [dbo].[tblPitchStatus](
[PitchStatusID] [int] IDENTITY(1,1) NOT NULL,
[PitchID] [int] NOT NULL,
[StatusTypeID] [int] NOT NULL,
[StatusDate] [varchar](50) NULL,
[StatusNotes] [varchar](max) NULL)

PitchStatusTypes
CREATE TABLE [dbo].[tblPitchStatusTypes](
[StatusTypeID] [int] IDENTITY(1,1) NOT NULL,
[StatusType] [varchar](50) NOT NULL,
[StatusOrder] [int] NOT NULL,
[StatusDescription] [varchar](255) NOT NULL)

I need to get records from PitchStatus with the most recent date and show the StatusType from PitchStatusTypes. This gets me the records with the min date

SELECT tblPitchStatus.PitchID, Min(tblPitchStatus.StatusDate) AS MinOfStatusDate
FROM tblPitchStatus INNER JOIN tblPitchStatusTypes ON tblPitchStatus.StatusTypeID = tblPitchStatusTypes.StatusTypeID
GROUP BY tblPitchStatus.PitchID;

When I add the StatusType from PitchStatusTypes I get all records that match.

SELECT tblPitchStatus.PitchID, Min(tblPitchStatus.StatusDate) AS MinOfStatusDate, tblPitchStatusTypes.StatusType
FROM tblPitchStatus INNER JOIN tblPitchStatusTypes ON tblPitchStatus.StatusTypeID = tblPitchStatusTypes.StatusTypeID
GROUP BY tblPitchStatus.PitchID, tblPitchStatusTypes.StatusType;

Can anyone show me how to write the query to show StatusType with the min date from PitchStatus?

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 11:20:41

[code]SELECT t.PitchID,t.StatusType
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY PitchID ORDER BY StatusDate) AS RowNo,ps.PitchID,st.StatusType
FROM tblPitchStatus ps
INNER JOIN PitchStatusTypes st
ON st.StatusTypeID=ps.StatusTypeID
)t
WHERE t.RowID=1[/code]

tpayne
Starting Member

18 Posts

Posted - 2008-08-13 : 13:42:19

Wow! Great response time and the query worked!! I made a mistake, however when describing the tables. tblPitches is joined to tblPitchStatusTypes by the table tblPitchStatus.
Here is tblPitches.
CREATE TABLE [dbo].[tblPitches](
[PitchID] [int] IDENTITY(1,1) NOT NULL,
[CommunicationTypeID] [int] NOT NULL,
[IndustryID] [int] NOT NULL,
[ProposalIdeaID] [int] NULL,
[VenueID] [int] NULL,
[VenueContact] [varchar](255) NULL,
[VenueNotes] [varchar](max) NULL,
[WorkingTitle] [varchar](255) NOT NULL,
[PitchDraftDate] [varchar](50) NULL,
[PitchTargetDate] [varchar](50) NULL,
[PitchSendDate] [varchar](50) NULL,
[PublishedTitle] [varchar](255) NULL,
[ArticleDraftDueDate] [varchar](50) NULL,
[ArticlePubDueDate] [varchar](50) NULL,
[ArticleSentDate] [varchar](50) NULL,
[ArticleEditorRevDate] [varchar](50) NULL,
[ArticlePublishDate] [varchar](50) NULL,
[PitchNotes] [varchar](max) NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [varchar](50) NOT NULL,
[LastUpdated] [datetime] NOT NULL,
[LastUpdatedBy] [varchar](50) NOT NULL,
[Deleted] [bit] NOT NULL)

So, i need to get pitchID and some other fields from tblPitches, but constrained by the min StatusDate from tblPitchStatus and show the StatusType from tblPitchStatusTypes.

Whew. Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:47:11

[code]SELECT t.*
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY PitchID ORDER BY StatusDate) AS RowNo,p.*,ps.PitchID,st.StatusType
FROM tblPitches p
INNER JOIN tblPitchStatus ps
ON ps.PitchID=p.PitchID
INNER JOIN tblPitchStatusTypes st
ON st.StatusTypeID=ps.StatusTypeID
)t
WHERE t.RowID=1[/code]

replace * with columns you want

EDIT:missed a space

tpayne
Starting Member

18 Posts

Posted - 2008-08-13 : 14:02:49

Error came up.
Ambigous column PitchID. The column PitchID was specified multiple times for t.

Is it because of the p.* and ps.PitchID?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 14:04:10

quote:
Originally posted by tpayne

Error came up.
Ambigous column PitchID. The column PitchID was specified multiple times for t.

Is it because of the p.* and ps.PitchID?

its because of p.* thats why i told to replace * with columns you want. both inner & outer query

tpayne
Starting Member

18 Posts

Posted - 2008-08-13 : 14:43:35

Oops. Thanks, for both the amazingly fast response and code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 14:44:41