July 2, 2008 at 10:24 am
I am attempting to write what is for me a very large query. The query will eventually get much more complex so I am trying to break it down into smaller steps. Here is my initial query which works just fine:
SELECT ci.StmtNum, s.Stmt, s.Count_Range
FROM tblClientImport ci INNER JOIN
tblStatements s ON ci.StmtNum = s.StmtNum
WHERE (tblClientImport.SurveyNum = 1840)
ORDER BY ci.ReportNum
Result Set is like:
1444 Text1 C
1445 Text2 C
1842 Text3 R
Now for my first problem, I have another table that I need to get data from based on this result set. If Count_Range = C then I need to run this query and return it in the same dataset as above:
SELECT sc.CharNum, c.Characteristic
FROM tblStmtChar sc INNER JOIN
tblCharacteristics c ON sc.CharNum = c.CharNum
WHERE (sc.SurveyNum = 1840) AND (sc.StmtNum = 1444)
This needs to be recursive for the first result set so after it pulls the data for Stmt - 1444 it then needs to get the data for 1445.
I am using SQL Server 2005 and VB .NET 2005.
Thanks in advance!!
July 2, 2008 at 10:56 am
Without the create table statements, it is not clear how this should be solved, but here is one possibility:
SELECTtblClientImport.StmtNum
,tblStatements.Stmt
,tblStatements.Count_Range
,tblStmtChar.CharNum
,tblCharacteristics.Characteristic
FROMtblClientImport
INNER JOIN tblStatements
ON tblClientImport.StmtNum = tblStatements.StmtNum
LEFT OUTER JOIN
tblStmtChar
on tblStmtChar.SurveyNum = tblClientImport.SurveyNum
and tblStatements.Count_Range = 'C'
INNER JOIN tblCharacteristics
on tblCharacteristics.CharNum = tblStmtChar.CharNum
WHERE tblClientImport.SurveyNum = 1840
SQL = Scarcely Qualifies as a Language
July 2, 2008 at 10:59 am
Rather than use a recursive query , why don't you use another inner-join
SELECT ci.StmtNum, s.Stmt, s.Count_Range,sc.CharNum, c.Characteristic
FROM tblClientImport ci INNER JOIN
tblStatements s ON ci.StmtNum = s.StmtNum
Inner join tblStmtChar sc on ci.StmtNum = sc.StmtNum
Inner join tblCharacteristics c ON sc.CharNum = c.CharNum
WHERE (tblClientImport.SurveyNum = 1840)
ORDER BY ci.ReportNum
or something similar to that, no promises it correct but post some more data and i can provide a correct one...
July 2, 2008 at 11:38 am
Thanks for the quick responses.
In the original query there are four statments that have a Count_Range value of "C". Within tblStmtChar a CharNum from tblCharacteristics can be in multiple SurveyNums. I think that is what is causing problems with both of the reply result sets.
Carl Federl:
10692 Text1 C 401 Monday
18746 Text2 C 401 Monday
18747 Text3 C 401 Monday
9834 Text4 C 401 Monday
10692 Text1 C 402 Tuesday
18746 Text2 C 402 Tuesday
18747 Text3 C 402 Tuesday
9834 Text4 C 402 Tuesday
steveb, yours is closer to what I need:
18746 Text2 C 401 Monday
18746 Text2 C 401 Monday
18746 Text2 C 402 Tuesday
18746 Text2 C 402 Tuesday
etc...
What I need is:
18746 Text2 C 401 Monday
18746 Text2 C 402 Tuesday
18746 Text2 C 402 Wednesday
etc..
18747 Text3 C 6326 English
18747 Text3 C 6327 Spanish
This eventually will be in a Stored Procedure that will be used by a Crystal Reports report.
July 2, 2008 at 12:38 pm
It appears to me, from the limited data given, that this should do what you need:
select distinct tblClientImport.StmtNum, tblStatements.Stmt,
tblStatements.Count_Range, tblStmtChar.CharNum,
tblCharactersists.Characteristic
from tblClientImport
inner join tblStatements
on tblClientImport.StmtNum = tblStatements.StmtNum
inner join tblStmtChar
on tblClientImport.StmtNum = tblStmtChar.StmtNum
and tblClientImport.SurveyNum = tblStmtChar.SurveyNum
inner join tblCharacteristics
on tblStmtChar.CharNum = tblCharacteristics.CharNum
where tblClientImport.SuveyNum = 1840
If it doesn't, please provide code for creating the tables, and insert statements for some sample data that can be used to produce your desired results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 12:59 pm
steveb: Your query gives me what I need when I added this to the WHERE clause - AND (sc.SurveyNum = 1840). That might not be the most eloquent way but it works.
July 2, 2008 at 1:17 pm
GSquared: Your solution worked also and will be easier to comprehend in the stored procedure as I will only have to put the survey number variable in the code once.
I see that a couple of you have asked for the code that created the tables but there is none. The tables come from an Access database that has been used for several years and had a lot of data in them. I recreated all of the tables in Management Studio and copied the data into SQL Server 2005 with a VB program. Not the cleanest way but as SQL Server is still very new to me that was the only way I knew how to do it at the time.
Well that solved the first part of my steps. I have something else pressing right now so I will post my next obstacle later tonight or first thing tomorrow.
July 2, 2008 at 1:19 pm
Good that it works.
I was only looking for table definition data if the query didn't work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 3, 2008 at 2:34 am
rconway (7/2/2008)
I see that a couple of you have asked for the code that created the tables but there is none.
Thanks for the feedback,
For future reference to obtain the table code you can right click on the table in SSMS and select
Script Table As --> Create to --> New Query Editor...
July 3, 2008 at 2:38 pm
I added a couple more columns that I needed and now here is my working query to the first problem:
select distinct ci.ReportNum, ci.StmtNum, ci.SchemeNum,
s.Stmt, s.Count_Range, sc.CharNum, c.Characteristic
from tblClientImport ci
inner join tblStatements s
on ci.StmtNum = s.StmtNum
inner join tblStmtChar sc
on ci.StmtNum = sc.StmtNum
and ci.SurveyNum = sc.SurveyNum
inner join tblCharacteristics c
on sc.CharNum = c.CharNum
where ci.SurveyNum = 1840
ORDER BY ci.ReportNum
Now this is where it starts getting crazy, for me anyway, maybe not for you guys. Based on the Count_Range value, if the value is "R" I need to pull different data from another table. Here is the base query for that:
SELECT ci.ReportNum, ci.StmtNum, s.Stmt, s.Count_Range, sv.Score, sv.Value, sv.Text
FROM tblClientImport ci INNER JOIN
tblScoreValue sv ON ci.SchemeNum = sv.SchemeNum INNER JOIN
tblStatements s ON ci.StmtNum = s.StmtNum
WHERE (s.Count_Range = N'R') AND (ci.SurveyNum = 1840)
ORDER BY ci.ReportNum
Here are the desired results for this query:
3.0 15001 Text3 R 0 0 No Opinion
3.0 15001 Text3 R 1 20 Unacceptable
3.0 15001 Text3 R 2 40 Poor
3.0 15001 Text3 R 3 60 Fair
3.0 15001 Text3 R 4 80 Good
3.0 15001 Text3 R 5 100 Excellent
5.0 15014 Text5 R 0 0 No Opinion
5.0 15014 Text5 R 1 20 Unacceptable
etc....
I think I will have to have a SELECT CASE somewhere in the query but I am not sure.Here are my desired results for the entire query:
1.0 18746 Text1 C 401 Monday
1.0 18746 Text1 C 402 Tuesday
1.0 18746 Text1 C 402 Wednesday
2.0 18747 Text2 C 6326 English
2.0 18747 Text2 C 6327 Spanish
3.0 15001 Text3 R NULL NULL 0 0 No Opinion
3.0 15001 Text3 R NULL NULL 1 20 Unacceptable
3.0 15001 Text3 R NULL NULL 2 40 Poor
3.0 15001 Text3 R NULL NULL 3 60 Fair
3.0 15001 Text3 R NULL NULL 4 80 Good
3.0 15001 Text3 R NULL NULL 5 100 Excellent
4.0 18748 Text4 C 6100 Yes NULL NULL NULL
4.0 18748 Text4 C 6101 No NULL NULL NULL
5.0 15014 Text5 R NULL NULL 0 0 No Opinion
5.0 15014 Text5 R NULL NULL 1 20 Unacceptable
etc...
I know this is long but I am adding my table scripts:
USE [Mercury]
GO
/****** Object: Table [dbo].[tblClientImport] Script Date: 07/03/2008 16:36:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblClientImport](
[ReportNum] [numeric](4, 1) NULL,
[ClientNum] [smallint] NOT NULL,
[StmtNum] [real] NOT NULL,
[BFrom] [real] NULL,
[BStmt] [nvarchar](250) NULL,
[CVCalc] [bit] NOT NULL,
[SurveyNum] [int] NOT NULL,
[SchemeNum] [int] NULL,
[SystemNum] [int] NULL,
CONSTRAINT [PK_tblClientImport] PRIMARY KEY CLUSTERED
(
[ClientNum] ASC,
[StmtNum] ASC,
[SurveyNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [Mercury]
GO
/****** Object: Table [dbo].[tblStatements] Script Date: 07/03/2008 16:37:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStatements](
[StmtNum] [int] NOT NULL,
[Stmt] [nvarchar](250) NULL,
[CVNum] [int] NULL,
[Count_Range] [nvarchar](1) NULL,
[Audio] [bit] NOT NULL,
[DStmtNum] [smallint] NULL,
[VerOne] [bit] NOT NULL,
[Emp] [bit] NOT NULL,
[OMN] [int] NULL,
[SPSSNum] [text] NULL,
CONSTRAINT [PK_tblStatements] PRIMARY KEY CLUSTERED
(
[StmtNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [Mercury]
GO
/****** Object: Table [dbo].[tblStmtChar] Script Date: 07/03/2008 16:37:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStmtChar](
[CSNum] [smallint] NOT NULL,
[CharNum] [int] NULL,
[StmtNum] [int] NOT NULL,
[SurveyNum] [int] NOT NULL,
CONSTRAINT [PK_tblStmtChar] PRIMARY KEY CLUSTERED
(
[CSNum] ASC,
[StmtNum] ASC,
[SurveyNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [Mercury]
GO
/****** Object: Table [dbo].[tblCharacteristics] Script Date: 07/03/2008 16:38:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCharacteristics](
[CharNum] [int] NOT NULL,
[Characteristic] [nvarchar](255) NULL,
[SuppChar] [nvarchar](50) NULL,
CONSTRAINT [PK_tblCharacteristics] PRIMARY KEY CLUSTERED
(
[CharNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [Mercury]
GO
/****** Object: Table [dbo].[tblScoreValue] Script Date: 07/03/2008 16:38:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblScoreValue](
[SchemeNum] [smallint] NOT NULL,
[Score] [smallint] NOT NULL,
[Value] [smallint] NULL,
[Text] [nvarchar](50) NULL,
[FullName] [nvarchar](50) NULL,
[Sequence] [smallint] NULL,
[SuppFullName] [nvarchar](50) NULL,
CONSTRAINT [PK_tblScoreValue] PRIMARY KEY CLUSTERED
(
[SchemeNum] ASC,
[Score] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply