CODEWITHSUNDEEP
sqlsql-server-2008
user3538102
user3538102

Reputation: 171

Search table based on infromation from another table

I have created a temporary that has been populated correctly but now I want to search another table based on two fields that are contained within my temporary table. These fields are Forename and Surname. But I want to search for multiple student names and quantities and return specified data! I think the problem will be better explained in the images below:

My Temporary Table

enter image description here

The Table I would like to search (Table2)

enter image description here

Once I have searched each student name I want to be returned with the students Forename, Surname Address, Pin and Score!

Below shows how I have been trying to achieve this without any luck!

Select TempTable.Forname, TempTable.Surmname, Table2.Address, Table2.Pin 
from TempTable
Where Exists ( Select * from Table2
               where Table2.Forname=TempTable.Forname and
               Table2.Surname=TempTable.Surname
              )

But it is returning me no results and I don't know why!

Upvotes: 0

Views: 36

Answers (3)

jarlh
jarlh

Reputation: 44805

The EXISTS is only used to "filter" result, it's columns aren't available outside the EXISTS.

You need a JOIN!

Select TempTable.Forname, TempTable.Surmname, Table2.Address, Table2.Pin 
from TempTable JOIN Table2 ON Table2.Forname=TempTable.Forname and Table2.Surname=TempTable.Surname;

Upvotes: 2

Mateus Viccari
Mateus Viccari

Reputation: 7709

If i understand correctly your question, the way to do it is just a simple join:

select TempTable.Forename, TempTable.Surname, Table2.Address, Table2.Pin 
from TempTable
inner join Table2 on Table2.Forename = TempTable.Forename and Table2.Surname = TempTable.Surname

Though i recommend you to have a primary key on the "Persons" table (Table2) and use this primary key to reference the records on TepTable

Upvotes: 2

TonyJenkins
TonyJenkins

Reputation: 35

Assuming you really have called the columns "Forname" you just need a simple join. This does it explicitly to keep close to your original:

SELECT TempTable.Forname, TempTable.Surmname, Table2.Address, Table2.Pin 
FROM TempTable tt, Table2 t2
WHERE tt.Forename = t2.Forename
AND tt.Surname = t2.Surname;

You could do the same with INNER JOIN.

This is all assuming that student names are unique.

Upvotes: 1

Related Questions