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.
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:
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;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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.
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.
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;
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?
What Version and compatibility level are you running at?
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
Try putting a SELECT in front of Blobid on line 3 of Brian's code.
OM Gang
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;
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial