SQL syntax

Aleks
Aleks used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
omgangIT Manager

Commented:
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

Author

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 Administrator
Top Expert 2005

Commented:
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

Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Not sure what air-code means

Author

Commented:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Blobid'.
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
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 Manager

Commented:
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

Author

Commented:
I am using SQL 2008R2
Database Administrator
Top Expert 2005
Commented:
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

Author

Commented:
Seems to have worked ... I am not following the logic of this query, you mind explaining ?
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
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

Author

Commented:
Ok so the above should return the top blobid from the left join and then the rest of the query ...
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
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".

Author

Commented:
Thx. I'll test on the server that has the issue later today.

Author

Commented:
Great !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial