SQL Server: Performing OUTER APPLY between two subqueries
0
I want to get results from two subqueries using an OUTER Apply but it does not work. Below I put the skeleton of the query i am trying to do as an example:
SELECT DISTINCT D.Field1, D.Field2, S.Field1, S.Field2
FROM (
SELECT Field1, Field2
FROM Table1 t1 INNER JOIN Table2 t2 on t1.CommonField = t2.CommonField
INNER JOIN Table3 t3 on t3.CommonField = t2.CommonField
LEFT JOIN Table4 t4 on t4.CommonField = t3.CommonField
WHERE t1.Date > '20181011'
) D OUTER APPLY
(
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date
) S
SQL Server parser give me an error:
Icorrect syntax near 'D'.
I am using SQL Server 2008.
sql-server sql-server-2008 sql-server-2008-r2 asked Nov 23 '18 at 13:26
user1624552user1624552
3,051853110
add a comment |
0
I want to get results from two subqueries using an OUTER Apply but it does not work. Below I put the skeleton of the query i am trying to do as an example:
SELECT DISTINCT D.Field1, D.Field2, S.Field1, S.Field2
FROM (
SELECT Field1, Field2
FROM Table1 t1 INNER JOIN Table2 t2 on t1.CommonField = t2.CommonField
INNER JOIN Table3 t3 on t3.CommonField = t2.CommonField
LEFT JOIN Table4 t4 on t4.CommonField = t3.CommonField
WHERE t1.Date > '20181011'
) D OUTER APPLY
(
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date
) S
SQL Server parser give me an error:
Icorrect syntax near 'D'.
I am using SQL Server 2008.
sql-server sql-server-2008 sql-server-2008-r2 asked Nov 23 '18 at 13:26
user1624552user1624552
3,051853110
2
The "skeleton" looks syntactically OK. Problem lies elsewhere.
– Salman A
Nov 23 '18 at 13:29
4
What's that GROUP BY doing in there? You're not selecting t5.Date and you're not aggregating...
– Lord Peter
Nov 23 '18 at 13:45
2
I suspect that this is an anonymised query (who would really name all their objectstable{n},field{n}, andcommonfieldand not go crazy after a few hours because they have no idea what is what anymore), and the OP has anonymised it so much it's become a valid query (apart from theGROUP BYissue that @LordPeter mentioned). Post your actual query, not one that represents it, but doesn't show the problem you're having.
– Larnu
Nov 23 '18 at 14:21
1
where is the relation between query s and query d?
– A ツ
Nov 23 '18 at 14:41
Hi all, very sorry. I have not posted the real query because it is confidential. I have solved it, it was a missing parenthesis. Very sorry again, and a lot of thanks for your suggestion and support.
– user1624552
Nov 26 '18 at 14:28
add a comment |
0
0
0
0
I want to get results from two subqueries using an OUTER Apply but it does not work. Below I put the skeleton of the query i am trying to do as an example:
SELECT DISTINCT D.Field1, D.Field2, S.Field1, S.Field2
FROM (
SELECT Field1, Field2
FROM Table1 t1 INNER JOIN Table2 t2 on t1.CommonField = t2.CommonField
INNER JOIN Table3 t3 on t3.CommonField = t2.CommonField
LEFT JOIN Table4 t4 on t4.CommonField = t3.CommonField
WHERE t1.Date > '20181011'
) D OUTER APPLY
(
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date
) S
SQL Server parser give me an error:
Icorrect syntax near 'D'.
I am using SQL Server 2008.
sql-server sql-server-2008 sql-server-2008-r2 asked Nov 23 '18 at 13:26
user1624552user1624552
3,051853110
I want to get results from two subqueries using an OUTER Apply but it does not work. Below I put the skeleton of the query i am trying to do as an example:
SELECT DISTINCT D.Field1, D.Field2, S.Field1, S.Field2
FROM (
SELECT Field1, Field2
FROM Table1 t1 INNER JOIN Table2 t2 on t1.CommonField = t2.CommonField
INNER JOIN Table3 t3 on t3.CommonField = t2.CommonField
LEFT JOIN Table4 t4 on t4.CommonField = t3.CommonField
WHERE t1.Date > '20181011'
) D OUTER APPLY
(
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date
) S
SQL Server parser give me an error:
Icorrect syntax near 'D'.
I am using SQL Server 2008.
sql-server sql-server-2008 sql-server-2008-r2 sql-server sql-server-2008 sql-server-2008-r2 asked Nov 23 '18 at 13:26
user1624552user1624552
3,051853110
asked Nov 23 '18 at 13:26
user1624552user1624552
3,051853110
asked Nov 23 '18 at 13:26
user1624552user1624552
3,051853110
asked Nov 23 '18 at 13:26
user1624552user1624552
3,051853110
asked Nov 23 '18 at 13:26
user1624552user1624552
3,051853110
3,051853110
2
The "skeleton" looks syntactically OK. Problem lies elsewhere.
– Salman A
Nov 23 '18 at 13:29
4
What's that GROUP BY doing in there? You're not selecting t5.Date and you're not aggregating...
– Lord Peter
Nov 23 '18 at 13:45
2
I suspect that this is an anonymised query (who would really name all their objectstable{n},field{n}, andcommonfieldand not go crazy after a few hours because they have no idea what is what anymore), and the OP has anonymised it so much it's become a valid query (apart from theGROUP BYissue that @LordPeter mentioned). Post your actual query, not one that represents it, but doesn't show the problem you're having.
– Larnu
Nov 23 '18 at 14:21
1
where is the relation between query s and query d?
– A ツ
Nov 23 '18 at 14:41
Hi all, very sorry. I have not posted the real query because it is confidential. I have solved it, it was a missing parenthesis. Very sorry again, and a lot of thanks for your suggestion and support.
– user1624552
Nov 26 '18 at 14:28
add a comment |
2
The "skeleton" looks syntactically OK. Problem lies elsewhere.
– Salman A
Nov 23 '18 at 13:29
4
What's that GROUP BY doing in there? You're not selecting t5.Date and you're not aggregating...
– Lord Peter
Nov 23 '18 at 13:45
2
I suspect that this is an anonymised query (who would really name all their objectstable{n},field{n}, andcommonfieldand not go crazy after a few hours because they have no idea what is what anymore), and the OP has anonymised it so much it's become a valid query (apart from theGROUP BYissue that @LordPeter mentioned). Post your actual query, not one that represents it, but doesn't show the problem you're having.
– Larnu
Nov 23 '18 at 14:21
1
where is the relation between query s and query d?
– A ツ
Nov 23 '18 at 14:41
Hi all, very sorry. I have not posted the real query because it is confidential. I have solved it, it was a missing parenthesis. Very sorry again, and a lot of thanks for your suggestion and support.
– user1624552
Nov 26 '18 at 14:28
2
2
The "skeleton" looks syntactically OK. Problem lies elsewhere.
– Salman A
Nov 23 '18 at 13:29
The "skeleton" looks syntactically OK. Problem lies elsewhere.
– Salman A
Nov 23 '18 at 13:29
4
4
What's that GROUP BY doing in there? You're not selecting t5.Date and you're not aggregating...
– Lord Peter
Nov 23 '18 at 13:45
What's that GROUP BY doing in there? You're not selecting t5.Date and you're not aggregating...
– Lord Peter
Nov 23 '18 at 13:45
2
2
I suspect that this is an anonymised query (who would really name all their objects
table{n}, field{n}, and commonfield and not go crazy after a few hours because they have no idea what is what anymore), and the OP has anonymised it so much it's become a valid query (apart from the GROUP BY issue that @LordPeter mentioned). Post your actual query, not one that represents it, but doesn't show the problem you're having.– Larnu
Nov 23 '18 at 14:21
I suspect that this is an anonymised query (who would really name all their objects
table{n}, field{n}, and commonfield and not go crazy after a few hours because they have no idea what is what anymore), and the OP has anonymised it so much it's become a valid query (apart from the GROUP BY issue that @LordPeter mentioned). Post your actual query, not one that represents it, but doesn't show the problem you're having.– Larnu
Nov 23 '18 at 14:21
1
1
where is the relation between query s and query d?
– A ツ
Nov 23 '18 at 14:41
where is the relation between query s and query d?
– A ツ
Nov 23 '18 at 14:41
Hi all, very sorry. I have not posted the real query because it is confidential. I have solved it, it was a missing parenthesis. Very sorry again, and a lot of thanks for your suggestion and support.
– user1624552
Nov 26 '18 at 14:28
Hi all, very sorry. I have not posted the real query because it is confidential. I have solved it, it was a missing parenthesis. Very sorry again, and a lot of thanks for your suggestion and support.
– user1624552
Nov 26 '18 at 14:28
add a comment |
1 Answer
1
active
oldest
votes
0
The code looks okay, except that you've used GROUP BY t5.Date, but selecting other columns such as Field1, Field2. Just add these columns into GROUP BY:
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date, t5.Field1, t5.Field2
Let me show a work example:
DECLARE @TableA TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL
);
DECLARE @TableB TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL,
tableARid BIGINT NOT NULL
)
INSERT INTO @TableA(value) VALUES('test');
INSERT INTO @TableB(value, tableARid) VALUES ('test1', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test2', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test3', 1);
SELECT
subQuery.id
, subQuery.value
, oa.id
FROM
(
SELECT
ta.id
, ta.value
FROM @TableA ta
) subQuery
OUTER APPLY
(
SELECT
tb.id
, tb.value
FROM @TableB tb
GROUP BY tb.id, tb.[value]
) oa
answered Nov 23 '18 at 20:17
StepUpStepUp
7,87784473
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447578%2fsql-server-performing-outer-apply-between-two-subqueries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
The code looks okay, except that you've used GROUP BY t5.Date, but selecting other columns such as Field1, Field2. Just add these columns into GROUP BY:
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date, t5.Field1, t5.Field2
Let me show a work example:
DECLARE @TableA TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL
);
DECLARE @TableB TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL,
tableARid BIGINT NOT NULL
)
INSERT INTO @TableA(value) VALUES('test');
INSERT INTO @TableB(value, tableARid) VALUES ('test1', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test2', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test3', 1);
SELECT
subQuery.id
, subQuery.value
, oa.id
FROM
(
SELECT
ta.id
, ta.value
FROM @TableA ta
) subQuery
OUTER APPLY
(
SELECT
tb.id
, tb.value
FROM @TableB tb
GROUP BY tb.id, tb.[value]
) oa
answered Nov 23 '18 at 20:17
StepUpStepUp
7,87784473
add a comment |
0
The code looks okay, except that you've used GROUP BY t5.Date, but selecting other columns such as Field1, Field2. Just add these columns into GROUP BY:
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date, t5.Field1, t5.Field2
Let me show a work example:
DECLARE @TableA TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL
);
DECLARE @TableB TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL,
tableARid BIGINT NOT NULL
)
INSERT INTO @TableA(value) VALUES('test');
INSERT INTO @TableB(value, tableARid) VALUES ('test1', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test2', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test3', 1);
SELECT
subQuery.id
, subQuery.value
, oa.id
FROM
(
SELECT
ta.id
, ta.value
FROM @TableA ta
) subQuery
OUTER APPLY
(
SELECT
tb.id
, tb.value
FROM @TableB tb
GROUP BY tb.id, tb.[value]
) oa
answered Nov 23 '18 at 20:17
StepUpStepUp
7,87784473
add a comment |
0
0
0
The code looks okay, except that you've used GROUP BY t5.Date, but selecting other columns such as Field1, Field2. Just add these columns into GROUP BY:
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date, t5.Field1, t5.Field2
Let me show a work example:
DECLARE @TableA TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL
);
DECLARE @TableB TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL,
tableARid BIGINT NOT NULL
)
INSERT INTO @TableA(value) VALUES('test');
INSERT INTO @TableB(value, tableARid) VALUES ('test1', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test2', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test3', 1);
SELECT
subQuery.id
, subQuery.value
, oa.id
FROM
(
SELECT
ta.id
, ta.value
FROM @TableA ta
) subQuery
OUTER APPLY
(
SELECT
tb.id
, tb.value
FROM @TableB tb
GROUP BY tb.id, tb.[value]
) oa
answered Nov 23 '18 at 20:17
StepUpStepUp
7,87784473
The code looks okay, except that you've used GROUP BY t5.Date, but selecting other columns such as Field1, Field2. Just add these columns into GROUP BY:
SELECT Field1, Field2
FROM Table5 t5 INNER JOIN Table6 t6 on t6.CommonField = t5.CommonField
INNER JOIN Table7 t7 on t7.CommonField = t6.CommonField
WHERE t5.Field4 = 'SomeWhat'
GROUP BY t5.Date, t5.Field1, t5.Field2
Let me show a work example:
DECLARE @TableA TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL
);
DECLARE @TableB TABLE (
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL,
tableARid BIGINT NOT NULL
)
INSERT INTO @TableA(value) VALUES('test');
INSERT INTO @TableB(value, tableARid) VALUES ('test1', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test2', 1);
INSERT INTO @TableB(value, tableARid) VALUES ('test3', 1);
SELECT
subQuery.id
, subQuery.value
, oa.id
FROM
(
SELECT
ta.id
, ta.value
FROM @TableA ta
) subQuery
OUTER APPLY
(
SELECT
tb.id
, tb.value
FROM @TableB tb
GROUP BY tb.id, tb.[value]
) oa
answered Nov 23 '18 at 20:17
StepUpStepUp
7,87784473
answered Nov 23 '18 at 20:17
StepUpStepUp
7,87784473
answered Nov 23 '18 at 20:17
StepUpStepUp
7,87784473
answered Nov 23 '18 at 20:17
StepUpStepUp
7,87784473
7,87784473
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447578%2fsql-server-performing-outer-apply-between-two-subqueries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
The "skeleton" looks syntactically OK. Problem lies elsewhere.
– Salman A
Nov 23 '18 at 13:29
4
What's that GROUP BY doing in there? You're not selecting t5.Date and you're not aggregating...
– Lord Peter
Nov 23 '18 at 13:45
2
I suspect that this is an anonymised query (who would really name all their objects
table{n},field{n}, andcommonfieldand not go crazy after a few hours because they have no idea what is what anymore), and the OP has anonymised it so much it's become a valid query (apart from theGROUP BYissue that @LordPeter mentioned). Post your actual query, not one that represents it, but doesn't show the problem you're having.– Larnu
Nov 23 '18 at 14:21
1
where is the relation between query s and query d?
– A ツ
Nov 23 '18 at 14:41
Hi all, very sorry. I have not posted the real query because it is confidential. I have solved it, it was a missing parenthesis. Very sorry again, and a lot of thanks for your suggestion and support.
– user1624552
Nov 26 '18 at 14:28