Solved

Query question

Posted on 2013-12-11
6
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39712044
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
ID: 39712215
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
ID: 39712218
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 13

Assisted Solution

by:Ashok
Ashok earned 125 total points
ID: 39712250
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 32

Accepted Solution

by:
awking00 earned 125 total points
ID: 39714110
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:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 39714662
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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