Solved

JOIN Two temporary tables to interigate a third

Posted on 2002-05-22
13
1,310 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
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 25

Expert Comment

by:Mr_Peerapol
Comment Utility
I don't understand your question at all
0
 

Author Comment

by:lux_interior
Comment Utility
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
Comment Utility
I don't understand your question at all
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
Comment Utility
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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 5

Accepted Solution

by:
spcmnspff earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for that. I figured out my last point myself.

Cheers

lux
0
 

Author Comment

by:lux_interior
Comment Utility
Thanks for that. I figured out my last point myself.

Cheers

lux
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now