Extending a long SQL with inner joins (server 2000)

Question

Hi,

Recently a very friendly user on stackoverflow helped me with this SQL:

 SELECT (SELECT TOP 1 
                a.id
           FROM vAnalysesHistory AS a 
          WHERE a.companyid = n.stockid 
       ORDER BY a.chosendatetime DESC) AS id,
        n.name, 
        (SELECT TOP 1 
                a.chosendatetime 
           FROM vAnalysesHistory AS a 
          WHERE a.companyid = n.stockid 
       ORDER BY a.chosendatetime DESC) AS chosendatetime
   FROM vStockNames AS n 

Which is working great. Now i want to extend this SQL. In the table vAnalysesHistory there is an attribute called analyseid. I would like to only get rows with analyseid = 3 for example.

My try:

 SELECT (SELECT TOP 1 
                a.id
           FROM vAnalysesHistory AS a 
          WHERE a.companyid = n.stockid AND analyseid = 3
       ORDER BY a.chosendatetime DESC) AS id,
        n.name, 
        (SELECT TOP 1 
                a.chosendatetime 
           FROM vAnalysesHistory AS a 
          WHERE a.companyid = n.stockid AND analyseid = 3
       ORDER BY a.chosendatetime DESC) AS chosendatetime
   FROM vStockNames AS n 

The problem is that there isnt analyses with analyseid = 3 for every row in vStockNames. Therefore some of the rows returned has null in id and chosendatetime.

Can you help me? I only want rows where there exists and analyse with analyseid for example analyseid = 3.

And oh ye, the server is 2000.

Thanks in advance

Answer 1

I used another approach:

SELECT     B.id, C.ChosenDateTime, N.name
FROM         (SELECT     companyid, MAX(chosendatetime) AS ChosenDateTime
                       FROM          vAnalysesHistory AS A
                       WHERE      (analyseid = 3)
                       GROUP BY companyid) AS C INNER JOIN
                      vAnalysesHistory AS B ON C.companyid = B.companyid AND C.ChosenDateTime = B.chosendatetime AND B.analyseid = 3 INNER JOIN
                      vStockNames AS N ON N.stockid = C.companyid
ORDER BY N.name

Answer 2

As it's a derived column you'd need to wrap this query up in another query, and filter the results. Alternatively an "exists" where clause should do it:

    SELECT (SELECT TOP 1 
                a.id
           FROM vAnalysesHistory AS a 
          WHERE a.companyid = n.stockid AND analyseid = 3
       ORDER BY a.chosendatetime DESC) AS id,
        n.name, 
        (SELECT TOP 1 
                a.chosendatetime 
           FROM vAnalysesHistory AS a 
          WHERE a.companyid = n.stockid AND analyseid = 3
       ORDER BY a.chosendatetime DESC) AS chosendatetime
   FROM vStockNames AS n 
   WHERE exists (
    select 
        1 
    from 
        vAnalysesHistory 
    where
        stockid = n.stockid 
        and analyseid = 3)