Solved

JOIN Two temporary tables to interigate a third

Posted on 2002-05-22
13
1,335 Views
Last Modified: 2007-11-27
I have a situation where I need to find all the records in one table that match all the records in one table and all the records in a second.

For example:

Temp Table 1 contains:
677
688
699

Temp Table 2 contains:
12
13
14
15
16

I only want to return the records from talbe three that match a combination of a and b so:

677 and 12 (OK)
688 and 12 (OK)
700 and 12 (NOT OK)

What I'm looking for is something similar to:


SELECT AnswerID FROM AppraisalDetails A JOIN #QuestionIDs TQ,#Appraisals TA ON A.QuestionID= TQ.QuestionID AND A.AppraisalID=TA.AppraisalID

Which isn't correct I know but you might get an idea of what I'm trying to achieve.

Hope this makes sense.

lux_interior
0
Comment
Question by:lux_interior
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 7027427
I don't understand your question at all
0
 

Author Comment

by:lux_interior
ID: 7027437
Mr Peerapol

I've just added something at the bottom of my question, any clearer?

Just let me know if not and I'll try again.
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 7027468
I don't understand your question at all
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 7027483
Not sure about your question, the following query will return records in table3 that table3.SomeCol1 exists in table1 and table3.SomeCol2 exists in table2

select *
  from table3 t3
  where exists (select 1 from table1 t1 where t1.SomeCol1=t3.SomeCol1) and
        exists (select 1 from table2 t2 where t2.SomeCol2=t3.SomeCol2)
0
 

Author Comment

by:lux_interior
ID: 7027493
Ok, I'll try again.

I have one temporary table that holds all the questions for a particular category. I have another temporary table that holds all the appraisals for a particular userID.

I have a third table that holds all the details for all appraisals.

I want to get information from the appraisalDetails table but only information that matches any combination of values in BOTH temporary tables. That is if a record exists in the appraisal details table that has a questionID that appears in the temporary questions table AND an appraisalID that appears in the temporary appraisals table then return it.

I've included what I have so far, don't know if I'm way off or close.

Alter Procedure "spAAverageRating"
/*

     

*/
     
     @CompetencyID int,
     @ParticipantID int,
     @SurveyID int

As
     set nocount on

     DECLARE @PossibleTopScore int, @ActualScore int, @NumberOfQuestions int

     --get total number of questions in competency
     SELECT @NumberOfQuestions = COUNT(CompetencyID) FROM CQRelationships WHERE CompetencyID=@CompetencyID

     --multiply total number of questons by our number of answers (which is fixed at 4 but could very easily be pulled from the DB)
     SELECT @PossibleTopScore = @NumberOfQuestions * 4

     --calculate the actual score by adding up all the responses for our participant
     --we need to get all appraisal id's and question id's for this competency
     CREATE TABLE #Appraisals(

          AppraisalID int

     )

     INSERT INTO #Appraisals(AppraisalID)
     SELECT ID FROM Appraisals WHERE ParticipantID=@ParticipantID AND SurveyID=@SurveyID
     

     CREATE TABLE #QuestionIDs(

          ID int IDENTITY,
          QuestionID int

     )

     INSERT INTO #QuestionIDs
     SELECT QuestionID FROM CQRelationships WHERE CompetencyID=@CompetencyID
     
     --get all the questions and answers for all appraisals from the deatils page
     CREATE TABLE #QuestionsAndAnswers(

          QuestionID int,
          AnswerID int

     )

     INSERT INTO #QuestionsAndAnswers(QuestionID,AnswerID)
     SELECT QuestionID,AnswerID FROM AppraisalDetails AD JOIN #Appraisals TA ON AD.AppraisalID = TA.AppraisalID

     --get all the answers for all questions in all appraisals
     SELECT AnswerID FROM AppraisalDetails A JOIN #Appraisals TA ON A.AppraisalID= TA.AppraisalID
     
     DROP TABLE #QuestionIDs
     DROP TABLE #Appraisals
     DROP TABLE #QuestionsAndAnswers

     return

Hope thats clearer.

lux
0
 

Author Comment

by:lux_interior
ID: 7027512
I just tried

     SELECT * FROM AppraisalDetails AD WHERE EXISTS( SELECT 1 FROM #Appraisals TA WHERE AD.AppraisalID=TA.AppraisalID) AND
     EXISTS (SELECT 1 FROM #QuestionIDs TQ WHERE AD.QuestionID=TQ.QuestionID)

But it returns no records

lux
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 100 total points
ID: 7027767
What's wrong with a three table join?

SELECT AD.*
FROM AppraisalDetails AD, #Appraisals TA, #QuestionIDs TQ
WHERE AD.AppraisallID = TA.AppraisalID AND
    AD.QuestionID=TQ.QuestionID


   
0
 
LVL 32

Expert Comment

by:bhess1
ID: 7028470
You can do this more clearly with INNER JOINS:

SELECT AnswerID FROM AppraisalDetails A
INNER JOIN #QuestionIDs TQ ON A.QuestionID= TQ.QuestionID
INNER JOIN #Appraisals TA ON A.AppraisalID=TA.AppraisalID

Comments:  An INNER JOIN will return records only when there are matches in all INNER JOINed tables.  So, given your theoretical example, if A.QID = 677 AND A.AID = 12, the record would be returned.  If either the QuestionID (QID) or the AppraisalID (AID) is not found in the temp tables, the AnswerID will not be returned.

0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 7028726
According to using EXISTS or INNER JOINS, MS SQL Server will optimize all of your query(If it can) and after I tried using EXISTS and INNER JOINS, it showed that they cost the same amount of estimated time (In your case).

The good reason to use EXISTS function is it is self-documeted. But in some relatively complex query it's better use INNER JOIN.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7028810
Comparing the exists correlated subqueries and inner joins (whether using ANSI Inner Join syntax or not),  the SQL server optimizer will always choose the same execution plan. The methods mentioned in the previous three posts are equivalent.  It's easy to get confused by the rich, verbose nature of the SQL/ANSI 92 syntax without realizing that the engine is actually doing the same thing - which really boils it down to matter of preference.
0
 

Author Comment

by:lux_interior
ID: 7029183
Ok. My mistake I was entering incorrect test data hence I wasn't getting any records back :( Once I'd spotted my mistake it was fine.

I have one more question before I award the points if thats ok.

I now have a single column recordset with which looks like this:

1
1
2
1
3
4
4
2
1
3


And I want to count the total occurences of each number but all my attempts so far keep totalling up the whole amount, or telling me how many different numbes there are. Is there a simple way of doing this?

lux
0
 

Author Comment

by:lux_interior
ID: 7029211
Thanks for that. I figured out my last point myself.

Cheers

lux
0
 

Author Comment

by:lux_interior
ID: 7029212
Thanks for that. I figured out my last point myself.

Cheers

lux
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question