is there anyway around this? - 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

General SQL Server Forums

New to SQL Server Programming

is there anyway around this?

Author

Topic

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-16 : 05:00:59

hi all, i was wondering if there is another way around the following as i get errors.

the code query is:


select ar_store_code
	from pgm_action_responses as ar
	where ar_story_id =
			(
				select pss_story_id
				from pgm_store_stories
				where pss_story_id > '7900'
			)

Im trying to return a complete list of pss_story_ids in the subquery, but the following error is being returned:

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

(0 row(s) affected)

Does anyone know if there is a way to return all ar_story_ids that are equal to the second query? Would i use 'IN'?

hope i have explained myself, if you have any queries please shout.

any help would be great,

regards
MG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 05:05:17

One simple way is


select ar_store_code
	from pgm_action_responses as ar
	where ar_story_id in
			(
				select pss_story_id
				from pgm_store_stories
				where pss_story_id > '7900'
			)

Madhivanan

Failing to plan is Planning to fail

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-16 : 05:27:48

Hi , thanks for the reply.

I thought as much, but it still doesnt bring back what i need. I think the issue is with the Where part of the subquery. Thank you though.

I need to return a selection of results form one table that are not in another table, based on a the id(xx_story_id) from another. This is what i currently have:


select 
	pss_store_code, store_name, st_id, st_title 
from
	pgm_store_stories as ss
		inner join stores as s
			on s.store_code = ss.pss_store_code
		inner join pgm_stories as st 
			on ss.pss_story_id = st.st_id
where 
	s.storeStatus = 'on' and st_id >= '7900' and s.store_code not in
(
	select ar_store_code
	from pgm_action_responses as ar
	where ar_story_id IN
			(
				select aq_story_id
				from pgm_action_questions
				where aq_story_id > '7900'
			)
)
order by  pss_store_code asc, st_id

But its not returning anything as the query needs to reference/cycle through all ar_story_id - but it doesnt.

Regards
MG

derach2000
Starting Member

37 Posts

Posted - 2009-12-16 : 06:05:02

if you wish to get the rows from one table that are not present in another you could use this

select distinct t.id
from table1 t left outer join table2 t2
on t.id = t2.id
where t2.id is null

hope it helps...

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-16 : 11:27:33

Hi, thanks for the post.

My knowledge is still lacking, please could you explain the "where t2.id is null"

What is its purpose?

Reagrds
MG

gavakie
Posting Yak Master

221 Posts

Posted - 2009-12-16 : 12:10:26

Its from the join, when you do that join its going to create nulls on that table, those nulls are what you want.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-16 : 12:22:03

You might find the following easier to understand (and it's a little more efficient)


SELECT 
	pss_store_code
	, store_name
	, st_id
	, st_title 
FROM
	pgm_store_stories AS ss
	INNER JOIN stores AS s ON s.store_code = ss.pss_store_code
	INNER JOIN pgm_stories AS st ON ss.pss_story_id = st.st_id
WHERE 
	s.storeStatus = 'on'
	AND st_id >= '7900'

	AND NOT EXISTS (
		SELECT 1
		FROM
			pgm_action_responses ar
			JOIN pgm_action_questions aq ON aq.aq_story_ID = ar.ar_story_ID
		WHERE
			aq.aq_story_Id > '7900'
			AND ar.ar_store_code = s.store_code
	)
ORDER BY
	pss_store_code ASC
	, st_Id

The NOT EXISTS clause is very fast and is a little easier to understand than your nested IN statements. I re factored your innermost IN query to an INNER JOIN instead.

If the query still doesn't return the right results then I think you'll have to give some example data.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-16 : 12:27:16

Hi charlie, thanks for that, looks good. Will give it a try and check back.

Regards
MG

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-17 : 04:45:44

Hi charlie,

Thanks for the post, i tried your query but alas, nothing is returned. the same issue i have been getting with other queries too. will plod on

Regards
MG

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-17 : 05:21:26

If you post some sample data and desired output then we can help you.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-17 : 05:31:49

Hi charlie,

By sample data, what do you require? A selection of info in some of the tables i assume?

Regards
MG

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-17 : 06:05:29

Ideally --

The table definitions of the tables in question, (easiest way is to scrip table as... in management studio ) sample data for the tables in question, the desired output from that set of data.

Regards,

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-17 : 06:10:48

Hi charlie,

I've just seen your query again and was looking at this:

pgm_action_responses ar
JOIN pgm_action_questions aq ON aq.aq_story_ID = ar.ar_story_ID

You'll have to forgive me as this may be a silly question, still learning the area and what not, but i thought this isn't possible to link tables unless they are initially designed / first set up that way?

The above tables are not set up in the db with a link to each other.

again apologies if i have got this wrong or indeed, appear to be smarmy in anyway.

Regards
MG

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-17 : 06:32:24

I'm not entirely sure what you mean...

quote:

You'll have to forgive me as this may be a silly question, still learning the area and what not, but i thought this isn't possible to link tables unless they are initially designed / first set up that way?

The above tables are not set up in the db with a link to each other.

Do you mean that there is no primary key / foreign key relationship?

keys implement CONSTRAINTS (limits to the data) and they model the way that you wish the tables to be related but that doesn't mean that you can't establish a relationship outside of these constraints.

I changed your orriginal code:


(
	select ar_store_code
	from pgm_action_responses as ar
	where ar_story_id IN
			(
				select aq_story_id
				from pgm_action_questions
				where aq_story_id > '7900'
			)
)

Here, if you think about it, what you are actually doing is a JOIN and establishing the relationship between pgm_action_questions.aq_story_Id and pgm_action_responses.ar_story_Id Your nested IN statements should be equivalent to:


SELECT
	ar.ar_store_code
FROM
	pgm_action_responses AS ar
	JOIN pgm_action_questions AS aq ON
		aq.aq_story_Id = ar_stort_Id
		AND aq_stort_ID > '7900'

Except for the fact that you may get more rows from the JOIN than you did using the IN (if there are multiple duplicate aq.aq_story_Id then you will form a 1 to many relationship.

Because this code block was then used to filter for a NOT IN that won't matter. I've then used a NOT EXISTS to eliminate the NOT IN.

If you post some sample data and table structure we can see that the whole sql statement should be equivalent.

If neither statement does what you want then either

1) Your data is not as you expect

2) You haven't described what you want exactly enough.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-17 : 06:39:50

Hi charlie,

oh i see, you'd like to see how each table has been set up?

i think you'll need 4 tables. they are:

tbl.1.stores (holds the info on all stores):

USE [web]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Stores](
[StoreId] [int] IDENTITY(1,1) NOT NULL,
[Store_Code] [varchar](5) NULL,
[Store_Name] [varchar](255) NULL,
[Store_RegionId] [int] NULL,
[StoreStatus] [varchar](3) NULL,
CONSTRAINT [PK_Stores_1] PRIMARY KEY CLUSTERED
(
[StoreId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Stores] UNIQUE NONCLUSTERED
(
[Store_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

tbl2. pgm_store_stories (hold info on stores and the story set to the store)

USE [web]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pgm_store_stories](
[id] [int] IDENTITY(1,1) NOT NULL,
[pss_story_id] [int] NULL,
[pss_store_code] [varchar](5) NULL,
[pss_section_id] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[pgm_store_stories] WITH CHECK ADD CONSTRAINT [FK_pgm_store_stories_pgm_stories] FOREIGN KEY([pss_story_id])
REFERENCES [dbo].[pgm_stories] ([st_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pgm_store_stories] CHECK CONSTRAINT [FK_pgm_store_stories_pgm_stories]
GO
ALTER TABLE [dbo].[pgm_store_stories] WITH CHECK ADD CONSTRAINT [FK_pgm_store_stories_Stores] FOREIGN KEY([pss_store_code])
REFERENCES [dbo].[Stores] ([Store_Code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pgm_store_stories] CHECK CONSTRAINT [FK_pgm_store_stories_Stores]

tbl3. pgm_stories (holds detilas on all stories, stores and act as link between all)

USE [web]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pgm_stories](
[st_id] [int] IDENTITY(1,1) NOT NULL,
[st_pub_id] [int] NULL,
[st_title] [varchar](150) NULL,
[st_copy] [varchar](5000) NULL,
[st_image] [varchar](150) NULL,
[st_image_url] [varchar](200) NULL,
[st_sequence] [int] NULL,
[st_section_id] [int] NULL,
[st_image_size] [int] NULL CONSTRAINT [DF_pgm_stories_st_image_size] DEFAULT (150),
[st_access_level] [int] NULL,
[st_date] [datetime] NULL CONSTRAINT [DF_pgm_stories_st_date] DEFAULT (getdate()),
[st_status] [int] NULL CONSTRAINT [DF_pgm_stories_st_status] DEFAULT (1),
[st_action_point] [bit] NULL CONSTRAINT [DF_pgm_stories_st_action_point] DEFAULT (0),
[st_release_date] [datetime] NOT NULL,
CONSTRAINT [PK_pgm_stories] PRIMARY KEY CLUSTERED
(
[st_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[pgm_stories] WITH CHECK ADD CONSTRAINT [FK_pgm_stories_pgm_sections] FOREIGN KEY([st_section_id])
REFERENCES [dbo].[pgm_sections] ([section_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pgm_stories] CHECK CONSTRAINT [FK_pgm_stories_pgm_sections]

tbl4.pgm_action_questions (table where a list of all questions, past and present sits)
USE [web]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pgm_action_questions](
[aq_id] [int] IDENTITY(1,1) NOT NULL,
[aq_text] [varchar](300) NULL,
[aq_story_id] [int] NULL,
[aq_sequence] [int] NULL,
[aq_release_date] [datetime] NULL,
[aq_valid_date] [datetime] NULL,
[aq_valid_until] [datetime] NULL,
[aq_expire_date] [datetime] NULL,
[aq_confirm_date] [datetime] NULL,
CONSTRAINT [PK_pgm_action_questions] PRIMARY KEY CLUSTERED
(
[aq_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[pgm_action_questions] WITH CHECK ADD CONSTRAINT [FK_pgm_action_questions_pgm_stories] FOREIGN KEY([aq_story_id])
REFERENCES [dbo].[pgm_stories] ([st_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pgm_action_questions] CHECK CONSTRAINT [FK_pgm_action_questions_pgm_stories]

tbl5 (table where all answers go once stores have completed the jobs to do)
USE [web]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pgm_action_responses](
[ar_id] [int] IDENTITY(1,1) NOT NULL,
[ar_question_id] [int] NULL,
[ar_answer_id] [int] NULL,
[ar_answer_value] [varchar](300) NULL,
[ar_store_code] [varchar](5) NULL,
[ar_rep_id] [int] NULL,
[ar_story_id] [int] NULL,
[ar_confirmdate] [datetime] NULL CONSTRAINT [DF_pgm_action_responses_ar_confirmdate] DEFAULT (getdate()),
[ar_status] [int] NULL CONSTRAINT [DF_pgm_action_responses_ar_status] DEFAULT (1),
[ar_ok] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[pgm_action_responses] WITH CHECK ADD CONSTRAINT [FK_pgm_action_responses_pgm_stories] FOREIGN KEY([ar_story_id])
REFERENCES [dbo].[pgm_stories] ([st_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pgm_action_responses] CHECK CONSTRAINT [FK_pgm_action_responses_pgm_stories]

Hope this is what you need.

please shout if you need more

Regards
MG

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-17 : 06:43:16

Okey that's part 1

Can you please provide some sample data and expected output.

At this point I still don't have a clear idea what you are trying to do. I only know the landscape you are trying to do it in.

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-17 : 06:57:32

Hi charlie,

Sorry, thats correct, i did mean that the was not a foreign key / primary key relationship between the two.

I didnt realise you could create relationships outside the boundries set up when you first create the tables.

sample data:
tbl stores
store_code range from 001-214 so can use these and use any names as they do not matter.

tbl store_stories

1179355 7901 001 37 (37 can stay as it is)
1179367 7902 002 37
1179390 7903 003 37
1179391 7904 004 37

tbl stories only main columns are included
st_id = 7901 -> 7904
st_tile = task one -> task four
st_copy just holds a description of the task
st_date = when task is set by admin user
st_release_date - when it goes live u can use 12-12-2009 for all doesnt matter
all other columns are not needed

tbl pgm_action_responses
ar_store_code 001->004
ar_story_id 7901 -> 7904
ar_confirmdate is when the user has completed the task.
other columns not needed

001 7901 2009-12-16 14:55:40.373

expected outcome:
if the query runs as it should, the expected outcome should bring back a list of stores that have still not completed the task, so for example:
Store 1
7900 task_name
7901 task_name
7903 task_name
total = 3 outstanding

Store 2
7901 task_name
7904 task name
total = 2 outstanding

store 3
7902 task_name
7904 task_name

will keep working on mine and your re-edit - thanks once again.
Regards
MG

derach2000
Starting Member

37 Posts

Posted - 2009-12-17 : 07:37:11

quote:
Originally posted by mind_grapes

Hi, thanks for the post.

My knowledge is still lacking, please could you explain the "where t2.id is null"

What is its purpose?

Reagrds
MG

the query performes the left outer join, meaning the result would be all paired rows and all the rows from the left table without pair, that is, only those that exist in the left table. The where t2.id is null, means to filter only those that do not have a pair in the second table

derach2000
Starting Member

37 Posts

Posted - 2009-12-17 : 07:45:02

quote:
Originally posted by mind_grapes

Sorry, thats correct, i did mean that the was not a foreign key / primary key relationship between the two.

I didnt realise you could create relationships outside the boundries set up when you first create the tables.

of course u can use any column you need. you use any colums that have a logical link. If you do not have foreign keys, you should use indexes to speed things up

derach2000
Starting Member

37 Posts

Posted - 2009-12-17 : 07:58:07

quote:
Originally posted by mind_grapes

expected outcome:
if the query runs as it should, the expected outcome should bring back a list of stores that have still not completed the task, so for

one quick question, how do you mark a completed task

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-17 : 09:23:04

Hi, thanks for the reply.

I assume you mean in the db? not the users that actually mark it?

the following is an example of a task that has been completed in the db.

|ar_id | ar_question_id |ar_answer_id | ar_answer_value | ar_store_code| ar_rep_id | ar_story_id |ar_confirmdate | ar_status |ar_ok|

1315612 | 7216 | 15639 | 15639 |346 | 8707 |7952 | 2009-12-17 08:44:09.197 |1| 1

I did originally think that the '1' (in bold, "ar_status") was something to work of, as it was likely(i thought) to denote whether the task had been done or not, so i looked for zeros against other stores and task_ids, which would have meant they had not been done-easy enouguh i thought. But it was later i found out that stores are only added to this table once they have completed the task and not before, and are given one once they are added.

hope this makes sense.

Regards
MG

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources