Sql Server : Select from 4 tables with distinct values

Question

Table BASECASE(CaseNo, PID)

CaseNo| PID
===========
10    | 21
11    | 23

Table CHECKUP(ChkNo, CaseNo, CTime, PID)

ChkNo| CaseNo| CTime                  | PID
============================================
1    | 10    | 13 Jan 2014 03:05:15 PM| 21
2    | 11    | 17 Jan 2014 04:13:25 PM| 23
3    | 10    | 29 Mar 2014 02:45:34 PM| 21

Table DIAGNOSIS(CheckupNumber, Region, Value)

ChkNo| Region| Value
=====================
1    | AAA   | CCC
1    | BBB   | AAA
1    | CCC   | CCC
2    | DDD   | HHH
3    | BBB   | BBB
3    | AAA   | BBB

Table PATIENT(PID, Name)

PID| Name
==========
21 | John
22 | Smith
23 | Harry

SQL

SELECT B.CaseNo AS CNum, B.PID AS PatientID, C.CTime AS CTime, P.Name AS Name, D.ChkNo AS CheckupNumber, MAX(D.Region+'     '+D.Value) AS Disease 
FROM BASECASE B, CHECKUP C, PATIENT P, DIAGNOSIS D 
WHERE D.ChkNo LIKE C.ChkNo AND C.CaseNo LIKE B.CaseNo AND P.PID LIKE B.PID AND C.CTime >='1 Jan 2014' AND C.CTime <'1 Jan 2015' AND (D.Region LIKE '%AAA%' OR D.Value LIKE '%AAA%') 
GROUP BY B.CaseNo, B.PID, C.CTime, P.Name, D.ChkNo 
ORDER BY C.CTime ASC

Expected Result :

CaseNo| CTime                   | PID| Name| DISEASE| ChkNo
===========================================================
10    | 13 Jan 2014 03:05:15 PM | 21 | John| AAA CCC| 1

Result Occurred:

CaseNo| CTime                   | PID| Name| DISEASE| ChkNo
===========================================================
10    | 13 Jan 2014 03:05:15 PM | 21 | John| AAA CCC| 1
10    | 29 Mar 2014 02:45:34 PM | 21 | John| AAA BBB| 3

Logic:
Here we need to see if Region or Value of DIAGNOSIS is '%AAA%',
If we find Region or Value is '%AAA%' then we need to display the corresponding(CaseNo, ChkNo, PID, Name, CTime FROM BASECASE, CHECKUP and PATIENT
But CaseNo should not be repeated.

Select
MAX(Region+' '+Value) from DIAGNOSIS[(Region or Value)='%AAA%']
DISTINCT(CaseNo), PatientID from BASECASE
CheckupNo, CTime from CHECKUP[CTime>'1 Jan 2014' and CTime<'1 Jan 2015']
Name from PATIENT

How do I to sort this problem?

Answer 1

try this :

SELECT * FROM
(
    SELECT B.CaseNo AS CNum, B.PID AS PatientID, C.CTime AS CTime, P.Name AS Name, D.ChkNo AS CheckupNumber, (D.Region + '     ' + D.Value ) AS Disease, ROW_NUMBER() OVER (PARTITION BY c.caseno ORDER BY c.ChkNo) rn FROM   DIAGNOSIS D
    JOIN CHECKUP C ON D.chkno = C.ChkNo
    JOIN BASECASE B ON B.CaseNo = C.CaseNo
    JOIN PATIENT P ON B.PID = P.PID
    WHERE
         C.CTime >= '1 Jan 2014' AND C.CTime < '1 Jan 2015'
         AND (D.Region LIKE '%AAA%' OR D.Value LIKE '%AAA%')
) AS t WHERE  rn = 1 

NOTE : You can change the condition ORDER BY c.ChkNo to something like ORDER BY c.CTime to get the one which has minimum CTime or ORDER BY c.CTime DESC to get the one which has maximum CTime among the rows which have same c.caseno.