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

omgang

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

Aleks

ASKER

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 Crowe

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

Aleks

ASKER

Not sure what air-code means

Aleks

ASKER

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Blobid'.

Brian Crowe

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?

omgang

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

Aleks

ASKER

I am using SQL 2008R2

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

Seems to have worked ... I am not following the logic of this query, you mind explaining ?

Brian Crowe

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

Aleks

ASKER

Ok so the above should return the top blobid from the left join and then the rest of the query ...

Brian Crowe

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

Aleks

ASKER

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

Aleks

ASKER

Great !