Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2009

    Unanswered: retrieving information for many tables??

    Hi all,

    I'm still new to SQL and seem to be struggling with trying to retrieve certain results from the database, and i was hoping someone could help me out.

    This explanation of the problem is long so apologies and please bare with me.

    I have a number of stores (shops) that are held in a table in the database, these shops are sent "jobs to do" in the form "questions" (held in another table). Once the store(shops) have completed a job they have to reply to the question. This reply is also saved to another table.

    What I need to do is to produce a list of stores that still have outstanding questions, together with a list of what the questions are.

    However, only stores that have completed the task are sent to the task complete table, yet they are all sent the same task to do.

    An additional problem is that the questions have been set for many years and so a response of yes against many of them. Therefore i need to filter by the date the question was set and by a response to the questions

    Here is some info on the tables used:

    1. tbl stores
    > columns: storeID, store_name

    linked to:

    2.tbl pgm_store_stories
    >columns: id, pss_story_id, pss_store_code, pss_section_id

    linked to:
    3 tbl pgm_stories (the questions / jobs to do)
    >columns: st_id, st_title (of the question), st_date, st_status

    linked to 4 and 5:

    4. tbl pgm_action_questions
    >columns: aq_id, aq_text, aq_story_id, aq_release_date, aq_confirm_date

    5. tbl pgm_action_responses
    >columns: ar_id, ar_question, ar_answer_id, ar_answer_value, ar_store_code, ar_story_id, ar_confirmdate, ar_status, ar_ok

    These tables contain all the information that is important in the question above.

    Im not the best with SQL but i thought i may have to use subqueries or something? would i need to find the stores first and then the questions?

    tried this but it kept bringing back the same store / shop over and over again, clearly its wrong:

    --    ,[pss_story_id]
    --	,st_title
    FROM [knowledge].[dbo].[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
    	left join pgm_action_responses as ar
    		on st.st_id = ar.ar_story_id
    WHERE  pss_store_code NOT IN --pss_story_id > '7913'
    	SELECT ar_store_code
    	FROM pgm_action_responses
    	WHERE ar_ok = '1'
    Hope this is what you need. If you need anything else please say.

    Last edited by mind_grapes; 12-02-09 at 11:02.

Posting Permissions

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