Unanswered: retrieving information for many tables??
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
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:
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'
WHERE ar_ok = '1'
Hope this is what you need. If you need anything else please say.