LOADING TEMP TABLES AND MULTIPLE SEARCHES

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

LOADING TEMP TABLES AND MULTIPLE SEARCHES

Author

Topic

spencer
Starting Member

5 Posts

Posted - 2008-05-06 : 05:25:46

Hi Guys,

I have to work with a poorly designed table :(, that has columns

ID INT,
thisID varchar(50) null,
parentID varchar(50) null,
Title varchar(255) null,
Description varchar(8000) null,
ProductType varchar(255) null,

The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:

id 1
thisID 3
parentid nuLL
DESCRIPTION: this is a question

id 20
thisID 3_1
parentID 3
DESCRIPTION: this is the answer to the question above

So I am writing a sproc that does this using a temp table. I got this far:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Spencer
-- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE TEMP
(
ID INT,
thisID varchar(50) null,
parentID varchar(50) null,
Title varchar(255) null,
DescriptionQ varchar(8000) null,
DescriptionA varchar(8000) null,
ProductType varchar(255) null,
)

SELECT
ID,
thisID,
parentID,
Title,
DescriptionQ,
DescriptionA,
ProductType
FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL
END
GO

This gets all my questions for that product type.

What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.

Any bright sparks out there that can help me?

Cheers

Vadivu
Starting Member

31 Posts

Posted - 2008-05-06 : 05:41:37

CREATE TABLE #TEMPUS
(
ID INT,
thisID varchar(50) null,
parentID varchar(50) null,
Title varchar(255) null,
DescriptionQ varchar(8000) null,
DescriptionA varchar(8000) null,
ProductType varchar(255) null,
)

INSERT INTO #TEMPUS
(
ID ,
thisID ,
parentID ,
Title ,
DescriptionQ ,
DescriptionA ,
ProductType
)
select

ID,
thisID,
parentID,
Title,
Description,
NULL,
ProductType
FROM
A2Z
WHERE
ProductType = 1 AND parentID IS NULL

declare @qid varchar(50)
set @qid = (select thisID from #TEMPUS)

insert into #TEMPUS
(
ID ,
thisID ,
parentID ,
Title ,
DescriptionQ ,
DescriptionA ,
ProductType
)
select
ID,
thisID,
parentID,
Title,
@qid,
Description,
ProductType
FROM
A2Z
WHERE
ProductType = 1 AND parentID =@qid

select * from #TEMPUS

CHeck and tell me if this is the way u need the ouput

spencer
Starting Member

5 Posts

Posted - 2008-05-06 : 06:33:51

Thanks alot.

The error meesage I get is:

Msg 512, Level 16, State 1, Procedure GetFAQs, Line 54
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

However, with further investigation the upper part of the query works, namely:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Spencer Barriball
-- Create date: 06/05/08
-- Description: Gets questions and assoicated answers
-- from the a2z table to match product type
-- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #TEMPUS
(
ID INT,
thisID varchar(50) null,
parentID varchar(50) null,
Title varchar(255) null,
DescriptionQ varchar(3500) null,
DescriptionA varchar(3500) null,
ProductType varchar(255) null, --note varchar
)

INSERT INTO #TEMPUS
(
ID ,
thisID ,
parentID ,
Title ,
DescriptionQ ,
DescriptionA ,
ProductType
)

select

ID,
thisID,
parentID,
Title,
Description,
NULL,
ProductType

FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL AND Description IS NOT NULL

This gets me this table ( just a little of teh table shown here as the actual table is large):
id, thisid, parentid, title, descriptionq, descriptiona, producttype

1 Panel1 NULL facilities FACILITIES NULL euparks
2 Panel3 NULL alterations to bookings ALTERATIONS TO BOOKINGS NULL euparks
3 Panel4 NULL arrival and departure ARRIVAL AND DEPARTURE NULL euparks
4 Panel5 NULL bed linen and towels BED LINEN & TOWELS NULL euparks
5 Panel6 NULL cancellations CANCELLATIONS NULL euparks

-------------------------------------------------

When adding:

DECLARE @qid varchar(50)

SET @qid = (SELECT thisID FROM #TEMPUS)
-----
INSERT INTO #TEMPUS
(
DescriptionA
)
SELECT
Description
FROM
A2Z
WHERE
parentID = @qid AND Description IS NOT NULL

to the end of the query i get the error message shown at start.

Thank you very much for your help so far, any ideas?

Vadivu
Starting Member

31 Posts

Posted - 2008-05-06 : 06:45:31

i thought there will be only only one question id for one producttype... made a few changes to my original query... pls make the necessary changes, try it out and tell me

CREATE TABLE #TEMPUS
(
rowId INT IDENTITY(1,1),
ID INT,
thisID varchar(50) null,
parentID varchar(50) null,
Title varchar(255) null,
DescriptionQ varchar(8000) null,
DescriptionA varchar(8000) null,
ProductType varchar(255) null,
)

INSERT INTO #TEMPUS
(
ID ,
thisID ,
parentID ,
Title ,
DescriptionQ ,
DescriptionA ,
ProductType
)
select

ID,
thisID,
parentID,
Title,
Description,
NULL,
ProductType
FROM
A2Z
WHERE
ProductType = 1 AND parentID IS NULL

DECLARE @curRow INT
DECLARE @finalRow INT

declare @qid varchar(50)

SET @curRow = 1
SET @finalRow = (select count(*) from #TEMPUS)

IF(@finalRow > 0)
BEGIN
WHILE (@curRow <= @finalRow)
BEGIN

set @qid = (select thisID from #TEMPUS WHERE rowId = @curRow)

insert into #TEMPUS
(
ID ,
thisID ,
parentID ,
Title ,
DescriptionQ ,
DescriptionA ,
ProductType
)
select
ID,
thisID,
parentID,
Title,
@qid,
Description,
ProductType
FROM
A2Z
WHERE
ProductType = 1 AND parentID =@qid
SET @curRow = @curRow +1
END
END
select * from #TEMPUS
drop table #TEMPUS

spencer
Starting Member

5 Posts

Posted - 2008-05-06 : 07:02:48

Thanks again, almost there

I now recieve (again a much shorter version):

1 1 Panel1 NULL facilities FACILITIES NULL euparks
2 2 Panel3 NULL alterations to bookings ALTERATIONS TO BOOKINGS NULL euparks
3 3 Panel4 NULL arrival and departure ARRIVAL AND DEPARTURE NULL euparks

....

47 7 Panel1_1 Panel1 NULL Panel1 Whilst we have done our utmost to ensure that the facilities ... inform you. euparks
48 8 Panel3_1 Panel3 NULL Panel3 Once a booking has been confirmed ... ticket. euparks
49 9 Panel4_1 Panel4 NULL Panel4 Start days are clearly ... confirmation. euparks

What I require is on one row the question (descriptionq) AND the answer (descriptiona), so to show what i mean from above:

1 1 Panel1 NULL facilities FACILITIES we have done our utmost to ensure that the facilities ... inform you. euparks

So each one row corresponds to a question AND its assiocated answer.

Thanks alot

Vadivu
Starting Member

31 Posts

Posted - 2008-05-06 : 07:19:16

will there be only one answer for one question?

spencer
Starting Member

5 Posts

Posted - 2008-05-06 : 07:21:30

quote:
Originally posted by Vadivu

will there be only one answer for one question?

Yes there will be

Vadivu
Starting Member

31 Posts

Posted - 2008-05-06 : 07:24:01

then i guess, instead of the insert statement within the while loop, the following update statement can be used.

update #TEMPUS
set
DescriptionA =(
select Description
FROM
A2Z
WHERE
ProductType = 1 AND parentID =@qid)
where thisid=@qid

spencer
Starting Member

5 Posts

Posted - 2008-05-06 : 07:42:44

Thank you very much, the issue was solved by Vadivu. The final solution (for achieve purposes) is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Spencer -- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #TEMPUS
(
rowId INT IDENTITY(1,1),
ID INT,
thisID varchar(50) null,
parentID varchar(50) null,
Title varchar(255) null,
DescriptionQ varchar(8000) null,
DescriptionA varchar(8000) null,
ProductType varchar(255) null,
)

INSERT INTO #TEMPUS
(
ID ,
thisID ,
parentID ,
Title ,
DescriptionQ ,
DescriptionA ,
ProductType
)

select

ID,
thisID,
parentID,
Title,
Description,
NULL,
ProductType
FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL

DECLARE @curRow INT
DECLARE @finalRow INT

declare @qid varchar(50)

SET @curRow = 1
SET @finalRow = (select count(*) from #TEMPUS)

IF (@finalRow > 0)
BEGIN
WHILE (@curRow <= @finalRow)
BEGIN

set @qid = (select thisID from #TEMPUS WHERE rowId = @curRow)

update #TEMPUS
set
DescriptionA =(
select Description
FROM
A2Z
WHERE
ProductType = @productType AND parentID =@qid)
where thisid=@qid

SET @curRow = @curRow +1
END
END
select * from #TEMPUS
drop table #TEMPUS

END
GO

Vadivu
Starting Member

31 Posts

Posted - 2008-05-06 : 07:55:31

i started with the procedure u posted, and also was confused ( i also missed that it was 1:1 relationship)... but i guess this can be done in a simple way... try this simple query also

select q.id, q.thisid, q.title, q.description as question,
a.description as answer, q.producttype
from a2z q inner join a2z a on q.thisid = a.parentid

where q.producttype=@productType

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources