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

LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
You need to replace
LEFT JOIN FormsBlb AS c ON c.Activityid = a.id

with something like

LEFT JOIN (SELECT TOP 1 * FROM FormsBlb) AS c ON c.Activityid = a.id

Try that.
OM Gang
AleksAuthor Commented:
This did not work because it is taking the TOP record from FormsBlb and there may be other entries after this one record. it has to be the top entry from those in which the left join exists. not top from ALL entries.

So the top of this joins:

LEFT JOIN FormsBlb AS c ON c.Activityid = a.id  

There may be 3 or 4 or more but we need to select only the top FormsBlb.Blobid  that DOES join with the Activity table and not the top from ALL records. Hope that makes sense.
Brian CroweDatabase AdministratorCommented:
Air-code so caution...

WITH cteBlob AS
(
	Blobid, Activityid, Blobdata,
		ROW_NUMBER() OVER(PARTITION BY Activityid ORDER BY Blobid DESC) AS RowNumber
	FROM FormsBlb
)
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 cteBlob AS c ON c.Activityid = a.id
			AND c.RowNumber = 1
        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

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Start Today!
AleksAuthor Commented:
Not sure what air-code means
AleksAuthor Commented:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Blobid'.
Brian CroweDatabase AdministratorCommented:
air-code means I'm coding without a net.  I don't have access to your schema so its difficult to catch syntax errors.  Every other word has a squiggly red line under it for me since none of the tables exist in my system.

What Version and compatibility level are you running at?
omgangIT ManagerCommented:
Air-code means he's writing from memory and not actually in an IDE; not tested.  (Kind of like air-guitar).

Try putting a SELECT in front of Blobid on line 3 of Brian's code.
OM Gang
AleksAuthor Commented:
I am using SQL 2008R2
Brian CroweDatabase AdministratorCommented:
Apparently I forgot "SELECT" inside the cte ugh

WITH cteBlob AS
(
	SELECT Blobid, Activityid, Blobdata,
		ROW_NUMBER() OVER(PARTITION BY Activityid ORDER BY Blobid DESC) AS RowNumber
	FROM FormsBlb
)
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 cteBlob AS c ON c.Activityid = a.id
			AND c.RowNumber = 1
        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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Seems to have worked ... I am not following the logic of this query, you mind explaining ?
Brian CroweDatabase AdministratorCommented:
A cte (Common-Table Expression) is just a convenient way to write a sub-query.  I find them much easier to read and maintain but they compile the same as omgang's response.  The only thing he forgot was an ORDER BY in the query to make sure that the correct record was the TOP 1.

If you run the code within the cte it may make more sense as the only thing I'm adding is a row number.

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
AleksAuthor Commented:
Ok so the above should return the top blobid from the left join and then the rest of the query ...
Brian CroweDatabase AdministratorCommented:
Yes, the addition of the "AND c.RowNumber = 1" is the part that insures that it only joins to a single record from the cte and the logic of the ROW_NUMBER windowed function determines which record is listed as "1".
AleksAuthor Commented:
Thx. I'll test on the server that has the issue later today.
AleksAuthor Commented:
Great !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.