So after much trial-and-error I was able to come up with two approaches that work at acceptable speeds.
With the approach used in the question example, I determined that there was unfortunately no way around repeating the joins:
WITH matched_books AS (
SELECT id, title FROM books
WHERE title LIKE 'The %'
related_authorships AS (
Use this, sql server really doesn't like your ORDER BY in the subquery.
And as tiolbe already in the cmment every derived table needs a alias
Select TOP 3 *
from ServiceProvider as sp
I think this might work... unless you expect multiple duplicates from stuffthatis (in which case, try joining to a subquery of SELECT DISTINCT).
select A.*, B.*
from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
LEFT JOIN stuffthatis c on b.name = c.name
(b.ToBe = 1 and a.Being = C.being)
(b.ToBe <> 1 and C.name is ...
Maybe this query would be better?
We would check stuffThatIs for every row, and then validate if
ToBe=1 and there is a.Being in stuffThatIs OR
ToBe<>1 and there isn't a.Being in stuffThatIs
FROM someStuff a
LEFT JOIN otherStuff b ON (a.x = b.x)
OUTER APPLY (
SELECT 1 [is_inside]
WHERE EXISTS(SELECT *
FROM stuffThatIs x
The data don't fit your wanted resukt, so i had t change the date#
Schema (SQLite v3.30)
CREATE TABLE table1 (
INSERT INTO table1
(`ID`, `Quantity`, `DATE`)
('1', '40', '2020/10/10'),
('2', '0', '2020/10/11'),
('3', '10', '2020/10/12');
Quantity - LAG ( Quantity, ...
You can use a outer SELECT or A cte to accomplish that
,SUM(Value1) AS Value1
,SUM(Value2) AS Value2
a.ma as Document
,a.ca as type
,SUM(a.num) as Value1
,coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as ...