Link two temp tables to get a result set

Becky Edwards
Becky Edwards used Ask the Experts™
on
Need to make a temp table look at the results from another temp table and only bring back results based on the list of people's ID's in the first temp table.  I have both queries built and bringing back results.  

Need to know how to link the two temp tables and bring back a result set based on both sets.

Example:
IF OBJECT_ID('tempdb..#TEMP1') IS NOT NULL
      DROP TABLE #TEMP1
GO
SELECT pat_mrn, pat_name, disch_date
INTO #TEMP1
FROM ...
WHERE...
-----then
IF OBJECT_ID('tempdb..#TEMP2') IS NOT NULL
DROP TABLE #TEMP2
GO
SELECT pat.MRN, lab.ID
INTO #TEMP2
FROM...
WHERE...
---now what?
I need the pat_mrn, pat_name, disch_date, lab_id but ONLY if the pat_mrn is in both sets of data.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database and Application Developer
Commented:
Select
t1.pat_mrn
, t1.pat_name
, t1.disch_date
, t2.lab_id
From #TEMP1 t1
inner join #TEMP2 t2
on t1.pat_mrn = t2.[pat.MRN]

Open in new window

Ryan ChongSoftware Team Lead

Commented:
i think it should be:

Select t1.pat_mrn , t1.pat_name , t1.disch_date , t2.lab_id From #TEMP1 t1 inner join #TEMP2 t2 on t1.pat_mrn = t2.MRN

instead?
Mike EghtebasDatabase and Application Developer

Commented:
I gave

on t1.pat_mrn = t2.[pat.MRN]

for Becky to link the tables in whatever form is possible. It was not clear to me what the joining columns exactly are.
Becky EdwardsEpic Clarity Developer

Author

Commented:
I only tried the first one and it worked perfectly.  Thank you both for your comments!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial