• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Query question

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
soozh
Asked:
soozh
4 Solutions
 
Dan CraciunIT ConsultantCommented:
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
 
AshokCommented:
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
 
John_VidmarCommented:
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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AshokCommented:
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
 
awking00Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now