retrieving information for many tables??

mind_grapes · #1 (**permalink**) 12-02-09, 10:54

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:

Code:

SELECT
	 store_code
	,store_name
--    ,[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.

Regards
MG

Internet Services

Web Development

Digital Marketing

Consumer Tech