Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: how can this be filtered correctly?

    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:

    Code:
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i got stuck trying to understand why you're using two FULL OUTER JOINs

    what's up with that?

    how can you have a row in pgm_store_stories that does ~not~ have a matching row in the stores table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    hi thanks for the reply.

    it was just for testing purposes, i was hoping to reurn all columns including nulls, i was just messing about with the query - nothing more.

    the two are linked by:
    on s.store_code = ss.pss_store_code

    or have i done it wrong?

    Regards
    MG

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you want INNER JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi, thanks for the repley. i was wondering if there is another way around the following as i get errors.

    the code query is:


    Code:
    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?

    I've used 'IN', as i thought it may be this, but again it returns blank?

    essentially i need to 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:

    Code:
    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
    hope i have explained myself, if you have any queries please shout.

    any help would be great,

    regards
    MG

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i can speak only for myself, but if i were to investigate this any further, i would need to see complete table layouts and sample data, in order to understand the tables

    also, you should qualify your columns -- it's next to impossible to understand someone else's query if it contains multiple tables but the columns aren't qualified (example: look at your SELECT clause, there's no way to tell which of the tables those columns come from)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi, thanks for the post.

    You mean put the name of the table before the columns to look for, like; tablename.columnname, etc..

    I can post the details of the tables if you like? Im strating to think it cant be done the way im currently doing it.

    Regards
    MG

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mind_grapes View Post
    Im strating to think it cant be done the way im currently doing it.
    maybe you could try re-explaining what you're trying to do, with a short description of what each table is for and how they relate logially
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi, thanks for the reply.

    Ok, i shall try my best, please bare with me, i shall try to be concise.

    Background:
    Stores / shops (I'll refer to them as stores throughout) are sent tasks (aka, jobs "to do", but refered to as "story" in db) to do.

    Stores names and their store code, are held in one table. The tasks are held in another table.

    Now, once a task have been completed by a store, the store will mark the task as done. Once it has been marked as done, this store, along with the task number, is sent to another table (pgm_action_responses table)- to show that this particular task has been completed by the store. eg.

    answer_id | store_code | task_id | Date completed
    1314716 | 141 | 7947 | 2009-12-14 11:32:53.470

    but the other outstanding task to stores are not sent through.so in order to get a list i need to cross ref the stores from the table which holds all the names, against the stores not in the responses table.

    The task:
    The task is to produce a list has names of stores with the task that is still to be done.

    This sounds simple because the following would be enough for one task:
    Code:
    select store_code, store_name 
    from stores as s
    where storeStatus = 'on' and store_code not in
    (
    	select ar_store_code
    	from pgm_action_responses
    	where task_id  ='7944'
    ) 
    order by store_code ASC
    However, if i dont specify the task number the query does not work.

    Because so many tasks are sent to stores: task 1 sent to 100 stores, and then task2 also sent to another 100 stores. Eventually a store may complete task 1, but not task 2.

    And so, if the where task_id = '7944' is changed to where task_id > '7944' the store name will appear in the responses table for one of the tasks, and so it will not be returned in the query.

    I need to return stores based on the task id number as well as the store - both need to be considered to return the correct result, else it doesnt work.

    did think this would work:

    Code:
    select 
         pss_store_code
    	,store_name
    	,st_id
    	,st_title 
    from
    	pgm_store_stories as ss
    		left join stores as s
    			on s.store_code = ss.pss_store_code
    		left join pgm_stories as st 
    			on ss.pss_story_id = st.st_id
    where 
    	s.storeStatus = 'on' and st_id >= '7940' and (pss_store_code + st_id) not in
    (
    	select ar_store_code
    	from pgm_action_responses as ar
    	where ar_id > '1314716' and ar_store_code <> 'ppf'
    )
    order by  pss_store_code asc, st_id
    but it brings back everything, even where task have been completed.

    The tables (in bold), and columns. Links between tables in colour:
    stores (holds store info)
    columns:
    storeid,
    store_code,
    store_name

    pgm_store_stories
    columns
    id,
    pss_story_id,
    pss_store_code
    pss_section_id

    pgm_stories
    columns:
    st_id,( linked to pgm_store_stories)
    st_pub_id
    st_title,
    st_date (date story is set)
    st_release_date(date story is release)

    pgm_action_responses
    columns:
    ar_id,
    ar_question_id,
    ar_answer_id,
    ar_store_code,
    ar_story_id,( linked to pgm_stories)
    ar_confirmdate,
    ar_status


    pgm_action_questions
    aq_id ( linked to pgm_stories)
    aq_story_id
    aq_release_date
    aq_confirm_date

    I hope i have explained mself well. please say if ive not.

    sorry if its windy and long.

    Regards
    MG

    Quote Originally Posted by r937 View Post
    maybe you could try re-explaining what you're trying to do, with a short description of what each table is for and how they relate logially

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mind_grapes View Post
    This sounds simple because the following would be enough for one task ...
    However, if i dont specify the task number the query does not work.
    please tell me more about this task number that you have to specify

    why do you have to specify a task number, and why does it have to be higher than a certain number?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi,

    The reason i've specified it to be higher than a certain number is because there are thousand and thousand of task numbers.

    This is not a new system, but very old. recently managment have asked for this list to be produced so its better to produce a list of all stores based on the last few weeks to keep the size down?

    I figured that if i can get all stores from the table stores, that are not in the action_responses table then thats half the job.

    but filtering the stores based on task_id is causing a problem.

    Hope this answer your question?

    regards
    MG

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You state that once a task is complete it is moved to this other table. Does that mean it is deleted from the tasks table? Or do you mean that you have to join this tasks table with this completed tasks table to find tasks that are not yet complete?
    Dave

  13. #13
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi, thanks for the post.

    Once a task is completed it is added to this other table (action_responses). its not deleted from the task table. All task that have ever been set are still there in the task table.

    I dont believe you have to join the two tables. As one table holds the task and the other the task after it is completed.

    Hope this answers your question, if not please shout.

    Thanks all really appreciated

    Regards
    MG

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mind_grapes View Post
    but filtering the stores based on task_id is causing a problem.
    why do you want to filter the stores by task_id?

    i guess the question is, which task_id? and why that one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    By your last answer to me then it sounds like what you want is to write the query in the following fashion:

    Code:
    select your stores and tasks
       from whatever tables
    where your key equals between your tables
       and not exists (select taskids that are completed)
    Though, it would be much easier if you had a flag in your tasks table that marked a task as completed. In fact, if there are/were enough completed tasks, then an index on this flag would help you find the uncompleted tasks even faster.
    Dave
    Last edited by dav1mo; 12-16-09 at 13:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •