CODEWITHSUNDEEP
sql
vyclarks
vyclarks

Reputation: 878

Select newest TOPIC for each SUBJECT in SQL Server 2008

I have a project about programming a forum.

I have 2 tables in the database:

All I want is:

+ select COUNT(*) from TOPIC as numTOPIC group by idSUB--> as Table A
+select TOP 1 titleTOP order by Time desc->  as newestTOP group by idSUB---> as Table B
+ Then JOIN 3 table A,B,SUBJECT--> C(idSUB,titleSUB,numTOPIC,newestTOP, idUser (who created the newest topic))

I've found the way to LEFT JOIN A,SUBJECT-> C(idSUB,titleSUB,numTOPIC) but I really don't know the right syntax to JOIN 3 Tables above.

SELECT  
    a.idSUB, a.titleSUB,
    COUNT(b.idSUB) numTOPIC 
FROM
    SUBJECT a
LEFT JOIN 
    TOPIC b ON a.idSUB = b.idSUB
GROUP BY 
    a.idSUB, a.titleSUB

I just want do this in only one query. Help!

UPDATE:

By the code of @John Bingham below, the output table cannot display the SUBJECT which doesn't have any TOPIC. I want all the TOPIC can be display.

SELECT 
    s.idSUB, s.titleSUB, a.numTOPIC, 
    isnull(b.newestTOP, '') as [Newest Topic], 
    isnull(b.idUser, '')
FROM 
    Subject s 
INNER JOIN 
    (SELECT 
         IDSub, Count(*) as NumTopic 
     FROM 
         Topic 
     GROUP BY IDSub) a ON s.IDSub = a.IDSub
LEFT JOIN 
    (SELECT 
         t.IDSub, t.titleTop as newestTop, t.idUser as [idUser] 
     FROM 
         Topic t 
     INNER JOIN 
         (SELECT IDSub, Max([Time]) as tm 
          FROM Topic 
          GROUP BY IDSub) x ON t.IDSub = x.IDSub
     WHERE t.[Time] = x.tm) b ON s.IDSub = b.IDSub

It's the right query but I want more exactly, help!

Upvotes: 2

Views: 312

Answers (2)

Madhusudan Joshi
Madhusudan Joshi

Reputation: 4476

You want to display idSub,titleSub,numTopic,newestTOP,idUser where numTopic is number of topics in any subject i.e idSub and newestTOP is latest topic for the same subject.

I am using MySQL and tested the following query and it works fine.

SELECT S.idSub, S.titleSUB, TOPIC_COUNT_TABLE.NUMTOPIC, NEWESTTOPIC_TABLE.NEWESTTOPIC,   
NEWESTTOPIC_TABLE.IDUSER 
FROM SUBJECT S,

(SELECT IDSUB, COUNT(*) AS NUMTOPIC FROM TOPIC 
GROUP BY TOPIC.IDSUB) AS TOPIC_COUNT_TABLE, 

(SELECT T.IDSUB,T.titleTOP AS NEWESTTOPIC, T.IDUSER  
FROM TOPIC T,(SELECT IDTOP,IDSUB,MAX(TIME) AS MAXTIME FROM TOPIC
GROUP BY IDSUB ) AS MAXTIME_TABLE
WHERE T.TIME = MAXTIME_TABLE.MAXTIME) AS NEWESTTOPIC_TABLE

WHERE S.IDSUB = NEWESTTOPIC_TABLE.IDSUB AND S.IDSUB = TOPIC_COUNT_TABLE.IDSUB;

Upvotes: 0

John Bingham
John Bingham

Reputation: 2006

My inclination is to convert each of the requirements (a), (b) & (c) into subqueries which deliver the virtual tables to your query, rather than attempting to combine the base tables to deliver the requirements in a singe hit. So -

SELECT s.idSUB, s.titleSUB, a.numTOPIC, isnull(b.newestTOP, '') as [Newest Topic], 
  isnull(b.idUser, '')
FROM Subject s 
  INNER JOIN (SELECT IDSub, Count(*) as NumTopic FROM Topic GROUP BY IDSub) a 
    ON s.IDSub = a.IDSub
  LEFT JOIN (
    SELECT t.IDSub, t.titleTop as newestTop, t.idUser as [idUser] 
    FROM Topic t 
      INNER JOIN (
        SELECT IDSub, Max([Time]) as tm FROM Topic GROUP BY IDSub
      ) x ON t.IDSub = x.IDSub
    WHERE t.[Time] = x.tm
  ) b ON s.IDSub = b.IDSub

Upvotes: 1

Related Questions