Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query question

Posted on 2013-12-11
6
Medium Priority
?
230 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 500 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 13

Assisted Solution

by:Ashok
Ashok earned 500 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 500 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 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