how can i refine this query? - 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

how can i refine this query?

Author

Topic

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-15 : 08:48:57

Hi all, hope you can help.

I posted a thread a while back and thought i had solved the problem i originally facing. However it seems i was mistaken.

I believe i now understand the issues i had, but don't know how to work it into the sql query.

The sql query needs to extract data from tables that will list all current tasks (jobs to do) that are still outstanding for a specific store/shop.
eg:

store 1,
task to do number 7943
task to do number 7944
task to do number 7945
total = 3

store 2,
task to do number 7943
task to do number 7944
task to do number 7945
total = 3

etc..

Only when a task has been completed by the store/shop, will it be sent to another table(pgm_action_responses ). So I have to cross ref the stores from the table that holds a complete list of stores(stores). Sounds easy enough, and i thought this would be ok:



select 
	pss_store_code, store_name, st_id, st_title 
from
	pgm_store_stories as ss
		full join stores as s
			on s.store_code = ss.pss_store_code
		full join pgm_stories as st
			on ss.pss_story_id = st.st_id
where 
	storestatus = 'on' and st_id > '7900' and pss_store_code not in
(
	select ar_store_code
	from pgm_action_responses 
	where ar_story_id > '7944'
)
order by pss_store_code, st_id

but this doesnt bring back the correct information. This is because the query searches for a store_code not in the pgm_actions_responses that is above '7944', and due to many tasks' being sent to many stores some task will be completed by stores and and their store number will then be sent to this pgm_action_responses table. So the query will pick up the stores that are in the database and not return the correct result.

When the, where ar_story_id > '7944' is taken out, nothiing is returned as the search is done through the whole table, and because of previous task the store code will already be in there.

So ideally i need to select all store_codes that are not present in the action_responses table where a specific task is unique to a specific store. Really cant work out how to do it?

Hope this all makes sense? if not please ask, and i shall try to answer your question.

Kind regards
MG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-20 : 03:58:41

what you need to do is post some data from your tables and then explain the problem with it for us to understand.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources