CODEWITHSUNDEEP
c#asp.netsql
Meensat
Meensat

Reputation: 55

How to select from 2 tables depending on uniqueness values in a column in one table

I have 2 tables: First (Cases) Second (Comments)

in one to many relation ; I'm storing the comments of each case in Comments table.

I need to select case information from Cases table which are presented in Comments table, but I want each case to be displayed ONCE ordered by comments added date (cDate)

I tried:

SELECT TOP 10  
     Cases.*,  
     comments.cDate  
FROM  
     Cases 
INNER JOIN comments 
     ON Cases.Case_ID = comments.Case_ID 
WHERE comments.Case_ID IN 
        (
           SELECT DISTINCT 
              Case_ID 
           FROM 
             comments
         ) 
 ORDER BY cDate DESC

but its retrieving the case multiple times if it has many comments. I need it to appear one time only

Thank you all, you helped alot ,, I just added 

Cases.Case_ID IN (SELECT Case_ID FROM comments)

and it worked perfectly.

Select statement is like this now: 

SELECT top 10 Cases.*,
(SELECT MAX(comments.cDate) 
       FROM comments 
       WHERE Cases.Case_ID = comments.Case_ID  ) AS cDate 
FROM Cases
WHERE Cases.Case_ID 
                 IN (SELECT Case_ID FROM comments) 
ORDER BY cDate DESC

Thanks once again :)

Upvotes: 0

Views: 142

Answers (4)

Meensat
Meensat

Reputation: 55

Thank you all, you helped alot ,, I just added

Cases.Case_ID IN (SELECT Case_ID FROM comments)

and it worked perfectly.

Select statement is like this now:

SELECT top 10 Cases.*,
(SELECT MAX(comments.cDate) 
       FROM comments 
       WHERE Cases.Case_ID = comments.Case_ID  ) AS cDate 
FROM Cases
WHERE Cases.Case_ID 
                 IN (SELECT Case_ID FROM comments) 
ORDER BY cDate DESC

Thanks once again :)

Upvotes: 0

Arion
Arion

Reputation: 31239

First some test data:

DECLARE @tblCases TABLE(Case_ID INT)
DECLARE @comments TABLE(Case_ID INT,cDate DATETIME)

INSERT INTO @tblCases
SELECT 1 UNION ALL SELECT 2

INSERT INTO @comments
SELECT 1,GETDATE() UNION ALL
SELECT 1,GETDATE()-1 UNION ALL
SELECT 2,GETDATE()-2 UNION ALL
SELECT 2,GETDATE()-3

Then i would do it like this.

;WITH CTE AS
(
    SELECT
        RANK() OVER(PARTITION BY Case_ID ORDER BY cDate DESC) AS iRank,
        tbl.cDate,
        tbl.Case_ID
    FROM
        @comments AS tbl
)
SELECT TOP 10  
     Cases.*,  
     CTE.cDate  
FROM  
    CTE
    JOIN @tblCases AS Cases
        ON Cases.Case_ID=CTE.Case_ID
WHERE
    CTE.iRank=1

Upvotes: 0

Tomek
Tomek

Reputation: 3279

This will do: 

SELECT TOP 10
        A.* ,
        (SELECT MAX(C.cDate) FROM comments C WHERE C.Case_ID = A.Case_ID) commDate
FROM    Cases A
        INNER JOIN comments B ON A.Case_ID = B.Case_ID
ORDER BY commDate

(@yhw42 query will return Cases which have no comments as well.)

Upvotes: 1

yhw42
yhw42

Reputation: 3434

In this case, it looks like you want to use a subquery:

SELECT top 10 Cases.*, 
(SELECT MAX(comments.cDate) FROM comments 
           WHERE Cases.Case_ID = comments.Case_ID ) AS cDate
FROM Cases 
ORDER BY cDate DESC

Upvotes: 1

Related Questions