Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    8

    Unanswered: How can I use more than one query to fill a temp table

    Hi all,
    Sorry if this is a newbie quest. I found a similar post but it did not fix my problem.

    I have three queries that I use to determine what the party type of a person.
    They will be either a child, family member or an associated party and I need to go to three different tables to find this out. I set a field "relationship" to child, family member or associated party depending on the query results.
    I would like to fill a temp table with the results so I can use it in reporting.
    Here is my code:
    Code:
    SELECT		vap.partyID, relationship
    	INTO		#Relationship
    	SELECT		vap.partyID, Relationship = 'Child'
    	FROM		VolunteerActivityParty vap JOIN
    				VolunteerActivity va ON va.VolunteerActivityID = vap.VolunteerActivityID JOIN
    				CourtReport cr ON cr.CaseID = va.CaseDetailID JOIN
    				CaseChild cc ON cc.ChildPArtyID = vap.PartyID  				
    	WHERE		cc.CaseChildID IN (SELECT caseChildID from CaseChild) AND cr.CourtReportID = 22
    	
    	
    	
    	SELECT		vap.partyID, Relationship = 'Family Member'
    	INTO		#Relationship
    	FROM		VolunteerActivityParty vap JOIN
    				VolunteerActivity va ON va.VolunteerActivityID = vap.VolunteerActivityID JOIN
    				CourtReport cr ON cr.CaseID = va.CaseDetailID JOIN
    				FamilyMember fm on vap.PartyID = fm.partyID		
    	WHERE		fm.PartyID  IN (SELECT PartyID from FamilyMember) AND cr.CourtReportID = 22
    	
    	UNION
    	
    	SELECT		vap.partyID, Relationship = 'Associated Party'
    	INTO		#Relationship
    	FROM		VolunteerActivityParty vap JOIN
    				VolunteerActivity va ON va.VolunteerActivityID = vap.VolunteerActivityID JOIN
    				CourtReport cr ON cr.CaseID = va.CaseDetailID JOIN
    				CaseAssociatedParty cap ON cap.AssociatedPartyID = vap.partyID 
    						
    	WHERE		cap.AssociatedPartyID  IN (SELECT AssociatedPartyID from CaseAssociatedParty) AND cr.CourtReportID = 22
    Any help is greatly appreciated
    Andy

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I have three queries that I use to determine what the party type of a person. They will be either a child, family member or an associated party AND I need to go to three different tables to find this out. <<

    Okay, but that does not imply separate queries …

    >> I set a field [sic: columns are not fields] "relationship" to child, family member or associated party depending on the query results. <<

    Really awful name for the “party_type”; too vague

    >> I would like to fill a temp table with the results so I can use it IN reporting. <<

    No, an SQL programmer hates temp tables that mimic scratch tapes or decks of punch cards. We might use a VIEW that keeps the status current whenever it is referenced. You are thinking Physical files, while we think logical and virtual tables

    >> Here is my code: <<

    Nice, but where is the DDL? Would you like to use ISO-11179 rules?

    Good SQL programmers do not use the infixed INNER JOIN syntax. ACCESS and procedural programmers like it because it is sequential. We set-oriented people like to re-arraign the FROM clause

    This lets us see the tables we need easily (it is called the law of Proximity in psychology). My guess is that you can re-arrange your UNIONs into a single CASE expression, something like this:

    SELECT VAP.party_id,
    CASE
    WHEN EXISTS(SELECT *
    FROM Case_Children AS CCX
    WHERE CCX.child_case_id
    = CC.child_case_id)
    THEN 'child'
    WHEN EXISTS(SELECT *
    FROM Familymembers AS FMX
    WHERE FMX.party_id
    = FM.party_id)
    THEN 'family member'
    WHEN EXISTS (SELECT associated_party_id
    FROM Case_Associated_Party AS CAPX
    WHERE CAPX.party_id
    = CAP.party_id)
    THEN 'associated party'
    ELSE 'unknown' END AS party_type
    FROM Volunteer_Activity_Party AS VAP,
    Volunteer_Activity AS VA,
    Court_Reports AS CR,
    Case_Children AS CC
    WHERE VA.volunteer_activity_id = VAP.volunteer_activity_id
    AND CR.case_id = VA.detail_case_id
    AND CC.child_party_id = VAP.party_id
    AND CR.court_report_id = 22;

  3. #3
    Join Date
    Jan 2013
    Posts
    8
    Hi,
    Thanks you very much for the help on this! I have one question.
    The query used in the reply seems to act as an inner join (in the results that it returns),
    returning data when there is information in both sides of the query.
    Is there a way to make it act like a left join returning records when only one side has information?
    Thanks again for you help.
    Andy

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    ROWS ARE NOT RECORDS! Please, please learn the difference; it is the first step to being a good SQL programmers.

    My guess, without any DDL, was that a party_type of 'child', 'family member' and 'associated party' were determined by separate tables (Case_Children AS CCX, Familymembers AS FMX, Case_Associated_Party AS CAPX); I added 'unknown' for safety.

    My CASE expression will evaluate the WHEN clauses in the order written, so if a party falls into more than type, you will not see it; the first one will be found and used.

    The EXISTS() can only return TRUE or FALSE; if a “party table” has a valid join in a WHEN clause then you will get TRUE and the output string.

  5. #5
    Join Date
    Jan 2013
    Posts
    8
    Hi,
    Thanks again for your reply. Everything is working perfectly now.
    Andy

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Glad to help. My wife and I adopted "Trouble Teenagers" and got invovled with Children Services but i never did a database for them.

Tags for this Thread

Posting Permissions

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