Solved

Query question

Posted on 2013-12-11
6
221 Views
Last Modified: 2014-01-08
Hello,

MS SQL Server 2008.

I have three tables joined by a column pat_id.

Table A contains one row per patient (pat_id).

Tables B and C contain many rows for pat_id.  They also contain a visitdate column.

What is the general syntax to join these tables so that I have Table A, plus the latest row from Table B and Table C when joining on pat_id?

Use visitdate to obtain the latest record in Tables B and C.

So basically I want for each patient their row from Table A, plus the latest rows from B and C.
0
Comment
Question by:soozh
6 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
I don't know on MS SQL, but on MySQL you would do something like:

SELECT fielda, fieldb, ..., fieldn,  MAX(TABLEB.visitdate)
FROM TABLEA
JOIN TABLEB ON TABLEA.pat_id = TABLEB.pat_id
GROUP BY TABLEA.pat_id

This will select the last field from TABLEB. If you need to join with TABLEC, D, E etc, subquery.

HTH,
Dan
0
 
LVL 13

Expert Comment

by:Ashok
Comment Utility
select pat_id, visitDt1 = (select Max(visitdate) from tableB t2 where t2.pat_id = a.pat_id),
visitDt2 = (select Max(visitdate) from tableC t3 where t3.pat_id = a.pat_id)
from tableA a

HTH
Ashok
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 125 total points
Comment Utility
Common Table Expressions (CTE) may be the answer:
with
	cteB(pat_id,visitdate)
	as
	(
		select	pat_id		
		,	max(visitdate)
		from	#tableB
		group
		by	pat_id
	)
,	cteC(pat_id,visitdate)
	as
	(
		select	pat_id		
		,	max(visitdate)
		from	#tableC
		group
		by	pat_id
	)
select	c.*
,	e.*
from	#tableA a
join	cteB	b	on	a.pat_id = b.pat_id
join	#tableB	c	on	b.pat_id = c.pat_id
			and	b.visitdate = c.visitdate
join	cteC	d	on	a.pat_id = d.pat_id
join	#tableC	e	on	d.pat_id = e.pat_id
			and	d.visitdate = e.visitdate

Open in new window

0
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

 
LVL 13

Assisted Solution

by:Ashok
Ashok earned 125 total points
Comment Utility
If you want other fields from TableB and TableC, use this.

select a.pat_id, b.*, c.*
from tableA a
left outer join tableB b on a.pat_id = b.pat_id
left outer join tableC c on a.pat_id = c.pat_id
where b.visitdate = (select Max(t2.visitdate) from tableB t2 where t2.pat_id = a.pat_id)
union all
select a.pat_id, b.*, c.*
from tableA a
left outer join tableB b on a.pat_id = b.pat_id
left outer join tableC c on a.pat_id = c.pat_id
where c.visitdate = (select Max(t3.visitdate) from tableC t3 where t3.pat_id = a.pat_id)
0
 
LVL 31

Accepted Solution

by:
awking00 earned 125 total points
Comment Utility
select a.pat_id, a.<otherfields>, b.<otherfields>, c.<otherfields> from
(select pat_id, <otherfields>
 from tablea a,
 (select pat_id, <otherfields>,
  row_number() over (partition by pat_id order by visitdate desc) rn
  from tableb) as b,
 (select pat_id, <otherfields>,
  row_number() over (partition by pat_id order by visitdate desc) rn
  from tablec) as c)
where a.pat_id = b.pat_id
  and a.pat_id = c.pat_id
  and b.rn = 1
  and c.rn = 1;
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
Comment Utility
SELECT A.*, B.*, C.*
FROM tableA A
INNER JOIN (
    SELECT
        pat_id, visitdate, --...other_column(s)_you_want_to_list...,
        ROW_NUMBER() OVER(PARTITION BY pat_id ORDER BY visitdate DESC) AS row_num
    FROM tableB
) AS B ON
    B.pat_id = A.pat_id AND
    B.row_num = 1
INNER JOIN (
    SELECT
        pat_id, visitdate, --...other_column(s)_you_want_to_list...,
        ROW_NUMBER() OVER(PARTITION BY pat_id ORDER BY visitdate DESC) AS row_num
    FROM tableC
) AS C ON
    C.pat_id = A.pat_id AND
    C.row_num = 1
ORDER BY
    A.pat_id
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

9 Experts available now in Live!

Get 1:1 Help Now