Solved

Query question

Posted on 2013-12-11
6
224 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

821 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