Avatar of Aleks
Aleks
Flag for United States of America

asked on 

SQL syntax

The query below displays a number of records, it has a left join to 'Formsblb', usually there is only one record there, but if for some reason there is more than one record then I get multiple results instead of just one.

I need to modify the query so it does a left join only with the top record to which it joins. The id of that table is 'Blobid' so the one with the higher value would be the top record. If its only one this should not be affected, but if there are multiple entries to which the left join would exist then instead of returning multiple entries it should return only one entry, the one where the Blobid is the highest value. I hope this makes sense. Here is the query:

SELECT  a.id ,
        a.caseid ,
        a.mainuserid ,
        a.petitionerid ,
        a.fname ,
        a.fdescription ,
        a.complete ,
        a.qid ,
        c.Blobid ,
        d.FirstNm ,
        d.MiddleNm ,
        d.LastNm ,
        CASE WHEN p.UserType = 'employer' THEN p.MaidenNm
             ELSE p.FirstNm + ' ' + p.MiddleNm + ' ' + p.LastNm
        END AS pet_name ,
        c.Blobdata
FROM    dbo.Formscase a
        LEFT JOIN Forms b ON a.fname = b.FormName
        LEFT JOIN FormsBlb AS c ON c.Activityid = a.id
        INNER JOIN Users AS d ON d.UserId = a.mainuserid
        LEFT JOIN Users AS p ON p.UserId = a.petitionerid
WHERE   a.firmid = 2
        AND a.caseid = 11337
        AND b.Qid IS NOT NULL
        AND b.IsObsolete = 0
ORDER BY fname ASC;

Open in new window

ASPMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Aleks

8/22/2022 - Mon