Sql server subquery to be rewritten using Joins
0
I have this pasted subquery and i need to rewrite it using only joins ( no subquery).
Tried multiple times for close to a month but in vain.
Request you to help me out.
SELECT * FROM wp_user WHERE userId NOT IN
(SELECT u.userId FROM wp_user as u, wp_luncher as i, wp_subscription as s
WHERE u.userId = i.luncherId
and i.luncherId = s.luncherid)
and CreationDate between '20181001' and '20181015';
Tables involved :-
CREATE TABLE wp_user (
userId int identity(1,1) PRIMARY KEY NOT NULL,
userName varchar(20) NOT NULL,
CreationDate date NOT NULL
);
CREATE TABLE wp_luncher (
luncherId int PRIMARY KEY NOT NULL,
parentId int FOREIGN KEY REFERENCES wp_user(userId)
);
CREATE TABLE wp_subscription (
SubId int PRIMARY KEY NOT NULL,
luncherId int FOREIGN KEY REFERENCES wp_luncher(luncherId)
);
sql-server asked Nov 15 '18 at 8:34
KrishiKrishi
1
add a comment |
0
I have this pasted subquery and i need to rewrite it using only joins ( no subquery).
Tried multiple times for close to a month but in vain.
Request you to help me out.
SELECT * FROM wp_user WHERE userId NOT IN
(SELECT u.userId FROM wp_user as u, wp_luncher as i, wp_subscription as s
WHERE u.userId = i.luncherId
and i.luncherId = s.luncherid)
and CreationDate between '20181001' and '20181015';
Tables involved :-
CREATE TABLE wp_user (
userId int identity(1,1) PRIMARY KEY NOT NULL,
userName varchar(20) NOT NULL,
CreationDate date NOT NULL
);
CREATE TABLE wp_luncher (
luncherId int PRIMARY KEY NOT NULL,
parentId int FOREIGN KEY REFERENCES wp_user(userId)
);
CREATE TABLE wp_subscription (
SubId int PRIMARY KEY NOT NULL,
luncherId int FOREIGN KEY REFERENCES wp_luncher(luncherId)
);
asked Nov 15 '18 at 8:34
KrishiKrishi
1
add a comment |
0
0
0
I have this pasted subquery and i need to rewrite it using only joins ( no subquery).
Tried multiple times for close to a month but in vain.
Request you to help me out.
SELECT * FROM wp_user WHERE userId NOT IN
(SELECT u.userId FROM wp_user as u, wp_luncher as i, wp_subscription as s
WHERE u.userId = i.luncherId
and i.luncherId = s.luncherid)
and CreationDate between '20181001' and '20181015';
Tables involved :-
CREATE TABLE wp_user (
userId int identity(1,1) PRIMARY KEY NOT NULL,
userName varchar(20) NOT NULL,
CreationDate date NOT NULL
);
CREATE TABLE wp_luncher (
luncherId int PRIMARY KEY NOT NULL,
parentId int FOREIGN KEY REFERENCES wp_user(userId)
);
CREATE TABLE wp_subscription (
SubId int PRIMARY KEY NOT NULL,
luncherId int FOREIGN KEY REFERENCES wp_luncher(luncherId)
);
asked Nov 15 '18 at 8:34
KrishiKrishi
1
I have this pasted subquery and i need to rewrite it using only joins ( no subquery).
Tried multiple times for close to a month but in vain.
Request you to help me out.
SELECT * FROM wp_user WHERE userId NOT IN
(SELECT u.userId FROM wp_user as u, wp_luncher as i, wp_subscription as s
WHERE u.userId = i.luncherId
and i.luncherId = s.luncherid)
and CreationDate between '20181001' and '20181015';
Tables involved :-
CREATE TABLE wp_user (
userId int identity(1,1) PRIMARY KEY NOT NULL,
userName varchar(20) NOT NULL,
CreationDate date NOT NULL
);
CREATE TABLE wp_luncher (
luncherId int PRIMARY KEY NOT NULL,
parentId int FOREIGN KEY REFERENCES wp_user(userId)
);
CREATE TABLE wp_subscription (
SubId int PRIMARY KEY NOT NULL,
luncherId int FOREIGN KEY REFERENCES wp_luncher(luncherId)
);
asked Nov 15 '18 at 8:34
KrishiKrishi
1
asked Nov 15 '18 at 8:34
KrishiKrishi
1
asked Nov 15 '18 at 8:34
KrishiKrishi
1
asked Nov 15 '18 at 8:34
KrishiKrishi
1
asked Nov 15 '18 at 8:34
KrishiKrishi
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
2
Try this query.
You can use OUTER APPLY.
SELECT *
FROM wp_user wp
OUTER APPLY
(
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = wp.userId
and i.luncherId = s.luncherid
) I
WHERE I.luncherId IS NOT NULL
and CreationDate between '20181001' and '20181015';
Another option inserts the subquery's data into a table variable.
DECLARE @TempTable AS TABLE (UserId INT)
INSERT INTO @TempTable
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = s.luncherid
SELECT *
FROM wp_user wp
LEFT JOIN @TempTable I ON I.UserId = wp.userId
WHERE I.UserId IS NOT NULL
and CreationDate between '20181001' and '20181015';
answered Nov 15 '18 at 8:39
Thilina NakkawitaThilina Nakkawita
9511228
@Krishi does that work?
– Thilina Nakkawita
Nov 15 '18 at 9:42
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%2f53315249%2fsql-server-subquery-to-be-rewritten-using-joins%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
2
Try this query.
You can use OUTER APPLY.
SELECT *
FROM wp_user wp
OUTER APPLY
(
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = wp.userId
and i.luncherId = s.luncherid
) I
WHERE I.luncherId IS NOT NULL
and CreationDate between '20181001' and '20181015';
Another option inserts the subquery's data into a table variable.
DECLARE @TempTable AS TABLE (UserId INT)
INSERT INTO @TempTable
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = s.luncherid
SELECT *
FROM wp_user wp
LEFT JOIN @TempTable I ON I.UserId = wp.userId
WHERE I.UserId IS NOT NULL
and CreationDate between '20181001' and '20181015';
answered Nov 15 '18 at 8:39
Thilina NakkawitaThilina Nakkawita
9511228
@Krishi does that work?
– Thilina Nakkawita
Nov 15 '18 at 9:42
add a comment |
2
Try this query.
You can use OUTER APPLY.
SELECT *
FROM wp_user wp
OUTER APPLY
(
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = wp.userId
and i.luncherId = s.luncherid
) I
WHERE I.luncherId IS NOT NULL
and CreationDate between '20181001' and '20181015';
Another option inserts the subquery's data into a table variable.
DECLARE @TempTable AS TABLE (UserId INT)
INSERT INTO @TempTable
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = s.luncherid
SELECT *
FROM wp_user wp
LEFT JOIN @TempTable I ON I.UserId = wp.userId
WHERE I.UserId IS NOT NULL
and CreationDate between '20181001' and '20181015';
answered Nov 15 '18 at 8:39
Thilina NakkawitaThilina Nakkawita
9511228
@Krishi does that work?
– Thilina Nakkawita
Nov 15 '18 at 9:42
add a comment |
2
2
2
Try this query.
You can use OUTER APPLY.
SELECT *
FROM wp_user wp
OUTER APPLY
(
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = wp.userId
and i.luncherId = s.luncherid
) I
WHERE I.luncherId IS NOT NULL
and CreationDate between '20181001' and '20181015';
Another option inserts the subquery's data into a table variable.
DECLARE @TempTable AS TABLE (UserId INT)
INSERT INTO @TempTable
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = s.luncherid
SELECT *
FROM wp_user wp
LEFT JOIN @TempTable I ON I.UserId = wp.userId
WHERE I.UserId IS NOT NULL
and CreationDate between '20181001' and '20181015';
answered Nov 15 '18 at 8:39
Thilina NakkawitaThilina Nakkawita
9511228
Try this query.
You can use OUTER APPLY.
SELECT *
FROM wp_user wp
OUTER APPLY
(
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = wp.userId
and i.luncherId = s.luncherid
) I
WHERE I.luncherId IS NOT NULL
and CreationDate between '20181001' and '20181015';
Another option inserts the subquery's data into a table variable.
DECLARE @TempTable AS TABLE (UserId INT)
INSERT INTO @TempTable
SELECT i.luncherId
FROM wp_luncher as i, wp_subscription as s
WHERE i.luncherId = s.luncherid
SELECT *
FROM wp_user wp
LEFT JOIN @TempTable I ON I.UserId = wp.userId
WHERE I.UserId IS NOT NULL
and CreationDate between '20181001' and '20181015';
answered Nov 15 '18 at 8:39
Thilina NakkawitaThilina Nakkawita
9511228
answered Nov 15 '18 at 8:39
Thilina NakkawitaThilina Nakkawita
9511228
answered Nov 15 '18 at 8:39
Thilina NakkawitaThilina Nakkawita
9511228
answered Nov 15 '18 at 8:39
Thilina NakkawitaThilina Nakkawita
9511228
9511228
@Krishi does that work?
– Thilina Nakkawita
Nov 15 '18 at 9:42
add a comment |
@Krishi does that work?
– Thilina Nakkawita
Nov 15 '18 at 9:42
@Krishi does that work?
– Thilina Nakkawita
Nov 15 '18 at 9:42
@Krishi does that work?
– Thilina Nakkawita
Nov 15 '18 at 9:42
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%2f53315249%2fsql-server-subquery-to-be-rewritten-using-joins%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
