Microsoft SQL Server 2008
Microsoft SQL Server 2008

Link two temp tables to get a result set

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.

Becky EdwardsEpic Clarity DeveloperAsked: 2015-09-16

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 34

Mike EghtebasDatabase and Application DeveloperCommented: 2015-09-16

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 57

Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented: 2015-09-16

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?

LVL 34

Mike EghtebasDatabase and Application DeveloperCommented: 2015-09-16

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.