JOIN Two temporary tables to interigate a third

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
lux_interiorAsked:
Who is Participating?
 
spcmnspffConnect With a Mentor Commented:
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
 
Mr_PeerapolCommented:
I don't understand your question at all
0
 
lux_interiorAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Mr_PeerapolCommented:
I don't understand your question at all
0
 
Mr_PeerapolCommented:
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
 
lux_interiorAuthor Commented:
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
 
lux_interiorAuthor Commented:
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
 
Brendt HessSenior DBACommented:
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
 
Mr_PeerapolCommented:
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
 
spcmnspffCommented:
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
 
lux_interiorAuthor Commented:
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
 
lux_interiorAuthor Commented:
Thanks for that. I figured out my last point myself.

Cheers

lux
0
 
lux_interiorAuthor Commented:
Thanks for that. I figured out my last point myself.

Cheers

lux
0
All Courses

From novice to tech pro — start learning today.