Solved

JOIN Two temporary tables to interigate a third

Posted on 2002-05-22
13
1,343 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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:Brendt Hess
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 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