SQL Query Except then select TOP 10
I have the following query where I EXCEPT two results in Microsoft SQL Server
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
The result has lots of records, but I only want the first 10 results. How can I accomplish this?
sql sql-server except
add a comment |
I have the following query where I EXCEPT two results in Microsoft SQL Server
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
The result has lots of records, but I only want the first 10 results. How can I accomplish this?
sql sql-server except
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql
,oracle
,sql-server
,db2
, ...
– a_horse_with_no_name
Nov 23 '18 at 9:01
1
Since you select all columns, the EXCEPT wont remove anything. NewsAudience.PortalId = 1 for first select, and 2 for second select.
– jarlh
Nov 23 '18 at 9:54
add a comment |
I have the following query where I EXCEPT two results in Microsoft SQL Server
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
The result has lots of records, but I only want the first 10 results. How can I accomplish this?
sql sql-server except
I have the following query where I EXCEPT two results in Microsoft SQL Server
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
The result has lots of records, but I only want the first 10 results. How can I accomplish this?
sql sql-server except
sql sql-server except
edited Nov 23 '18 at 9:52
jarlh
28.5k52138
28.5k52138
asked Nov 23 '18 at 8:56
FerronSWFerronSW
207
207
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql
,oracle
,sql-server
,db2
, ...
– a_horse_with_no_name
Nov 23 '18 at 9:01
1
Since you select all columns, the EXCEPT wont remove anything. NewsAudience.PortalId = 1 for first select, and 2 for second select.
– jarlh
Nov 23 '18 at 9:54
add a comment |
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql
,oracle
,sql-server
,db2
, ...
– a_horse_with_no_name
Nov 23 '18 at 9:01
1
Since you select all columns, the EXCEPT wont remove anything. NewsAudience.PortalId = 1 for first select, and 2 for second select.
– jarlh
Nov 23 '18 at 9:54
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql
, oracle
, sql-server
, db2
, ...– a_horse_with_no_name
Nov 23 '18 at 9:01
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql
, oracle
, sql-server
, db2
, ...– a_horse_with_no_name
Nov 23 '18 at 9:01
1
1
Since you select all columns, the EXCEPT wont remove anything. NewsAudience.PortalId = 1 for first select, and 2 for second select.
– jarlh
Nov 23 '18 at 9:54
Since you select all columns, the EXCEPT wont remove anything. NewsAudience.PortalId = 1 for first select, and 2 for second select.
– jarlh
Nov 23 '18 at 9:54
add a comment |
5 Answers
5
active
oldest
votes
Use LIMIT
:
Select *
FROM
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
ORDER BY News.PublishDate DESC
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
)a
LIMIT 10;
I tried something like that but I get an error at "a" The column "CustomerId" was specified multiple times for "a"
– FerronSW
Nov 23 '18 at 9:02
But the inner query runs fine?
– Mayank Porwal
Nov 23 '18 at 9:03
@FerronSW - you need to remove*
and list columns explicitly (good habit to be in anyway). SQL can return result sets to a client that contain multiple columns with the same name but balks at allowing such things in subqueries etc. (You at least need to do the two that form theexcept
, whether you also do the outer one is a matter of taste)
– Damien_The_Unbeliever
Nov 23 '18 at 9:05
Yes, it work just fine.
– FerronSW
Nov 23 '18 at 9:05
@Damien_The_Unbeliever Thanks that worked :)
– FerronSW
Nov 23 '18 at 9:15
add a comment |
use top
keyword as you are using sql server
select top 10 t.* from
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) t order by t.PublishDate desc
add a comment |
This is what I ended up with thanks to @Damien_The_Unbeliever and @Mayank Porwal
Select TOP(10) *
FROM
(
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) a
ORDER BY a.PublishDate DESC
That's great. But mark the appropriate responses as answers since that how SO is designed to work - questions are asked and then answered so that others having the same or similar question can find an answer.
– SMor
Nov 24 '18 at 13:22
add a comment |
this will work:
SELECT TOP 10 * from (SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC);
add a comment |
Your query doesn't make sense. You are getting all rows from the first subquery. Why? Because you are using select *
and NewsAudience.PortalId
is in the select list. That value is automatically different based on the conditions in the subqueries.
I would recommend phrasing your need differently. For instance, you might intend:
SELECT n.*
FROM News n
WHERE EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (1)
WHERE n.Id = na.NewsId
) AND
NOT EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (2)
WHERE n.Id = na.NewsId
)
ORDER BY n.PublishDate DESC;
You can add TOP (10)
to the outer query if duplicates are still a problem after you have correct the logic.
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
});
}
});
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%2f53443397%2fsql-query-except-then-select-top-10%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use LIMIT
:
Select *
FROM
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
ORDER BY News.PublishDate DESC
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
)a
LIMIT 10;
I tried something like that but I get an error at "a" The column "CustomerId" was specified multiple times for "a"
– FerronSW
Nov 23 '18 at 9:02
But the inner query runs fine?
– Mayank Porwal
Nov 23 '18 at 9:03
@FerronSW - you need to remove*
and list columns explicitly (good habit to be in anyway). SQL can return result sets to a client that contain multiple columns with the same name but balks at allowing such things in subqueries etc. (You at least need to do the two that form theexcept
, whether you also do the outer one is a matter of taste)
– Damien_The_Unbeliever
Nov 23 '18 at 9:05
Yes, it work just fine.
– FerronSW
Nov 23 '18 at 9:05
@Damien_The_Unbeliever Thanks that worked :)
– FerronSW
Nov 23 '18 at 9:15
add a comment |
Use LIMIT
:
Select *
FROM
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
ORDER BY News.PublishDate DESC
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
)a
LIMIT 10;
I tried something like that but I get an error at "a" The column "CustomerId" was specified multiple times for "a"
– FerronSW
Nov 23 '18 at 9:02
But the inner query runs fine?
– Mayank Porwal
Nov 23 '18 at 9:03
@FerronSW - you need to remove*
and list columns explicitly (good habit to be in anyway). SQL can return result sets to a client that contain multiple columns with the same name but balks at allowing such things in subqueries etc. (You at least need to do the two that form theexcept
, whether you also do the outer one is a matter of taste)
– Damien_The_Unbeliever
Nov 23 '18 at 9:05
Yes, it work just fine.
– FerronSW
Nov 23 '18 at 9:05
@Damien_The_Unbeliever Thanks that worked :)
– FerronSW
Nov 23 '18 at 9:15
add a comment |
Use LIMIT
:
Select *
FROM
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
ORDER BY News.PublishDate DESC
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
)a
LIMIT 10;
Use LIMIT
:
Select *
FROM
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
ORDER BY News.PublishDate DESC
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC
)a
LIMIT 10;
answered Nov 23 '18 at 9:00
Mayank PorwalMayank Porwal
4,5991624
4,5991624
I tried something like that but I get an error at "a" The column "CustomerId" was specified multiple times for "a"
– FerronSW
Nov 23 '18 at 9:02
But the inner query runs fine?
– Mayank Porwal
Nov 23 '18 at 9:03
@FerronSW - you need to remove*
and list columns explicitly (good habit to be in anyway). SQL can return result sets to a client that contain multiple columns with the same name but balks at allowing such things in subqueries etc. (You at least need to do the two that form theexcept
, whether you also do the outer one is a matter of taste)
– Damien_The_Unbeliever
Nov 23 '18 at 9:05
Yes, it work just fine.
– FerronSW
Nov 23 '18 at 9:05
@Damien_The_Unbeliever Thanks that worked :)
– FerronSW
Nov 23 '18 at 9:15
add a comment |
I tried something like that but I get an error at "a" The column "CustomerId" was specified multiple times for "a"
– FerronSW
Nov 23 '18 at 9:02
But the inner query runs fine?
– Mayank Porwal
Nov 23 '18 at 9:03
@FerronSW - you need to remove*
and list columns explicitly (good habit to be in anyway). SQL can return result sets to a client that contain multiple columns with the same name but balks at allowing such things in subqueries etc. (You at least need to do the two that form theexcept
, whether you also do the outer one is a matter of taste)
– Damien_The_Unbeliever
Nov 23 '18 at 9:05
Yes, it work just fine.
– FerronSW
Nov 23 '18 at 9:05
@Damien_The_Unbeliever Thanks that worked :)
– FerronSW
Nov 23 '18 at 9:15
I tried something like that but I get an error at "a" The column "CustomerId" was specified multiple times for "a"
– FerronSW
Nov 23 '18 at 9:02
I tried something like that but I get an error at "a" The column "CustomerId" was specified multiple times for "a"
– FerronSW
Nov 23 '18 at 9:02
But the inner query runs fine?
– Mayank Porwal
Nov 23 '18 at 9:03
But the inner query runs fine?
– Mayank Porwal
Nov 23 '18 at 9:03
@FerronSW - you need to remove
*
and list columns explicitly (good habit to be in anyway). SQL can return result sets to a client that contain multiple columns with the same name but balks at allowing such things in subqueries etc. (You at least need to do the two that form the except
, whether you also do the outer one is a matter of taste)– Damien_The_Unbeliever
Nov 23 '18 at 9:05
@FerronSW - you need to remove
*
and list columns explicitly (good habit to be in anyway). SQL can return result sets to a client that contain multiple columns with the same name but balks at allowing such things in subqueries etc. (You at least need to do the two that form the except
, whether you also do the outer one is a matter of taste)– Damien_The_Unbeliever
Nov 23 '18 at 9:05
Yes, it work just fine.
– FerronSW
Nov 23 '18 at 9:05
Yes, it work just fine.
– FerronSW
Nov 23 '18 at 9:05
@Damien_The_Unbeliever Thanks that worked :)
– FerronSW
Nov 23 '18 at 9:15
@Damien_The_Unbeliever Thanks that worked :)
– FerronSW
Nov 23 '18 at 9:15
add a comment |
use top
keyword as you are using sql server
select top 10 t.* from
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) t order by t.PublishDate desc
add a comment |
use top
keyword as you are using sql server
select top 10 t.* from
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) t order by t.PublishDate desc
add a comment |
use top
keyword as you are using sql server
select top 10 t.* from
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) t order by t.PublishDate desc
use top
keyword as you are using sql server
select top 10 t.* from
(
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) t order by t.PublishDate desc
answered Nov 23 '18 at 9:14
Zaynul Abadin TuhinZaynul Abadin Tuhin
11.5k2831
11.5k2831
add a comment |
add a comment |
This is what I ended up with thanks to @Damien_The_Unbeliever and @Mayank Porwal
Select TOP(10) *
FROM
(
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) a
ORDER BY a.PublishDate DESC
That's great. But mark the appropriate responses as answers since that how SO is designed to work - questions are asked and then answered so that others having the same or similar question can find an answer.
– SMor
Nov 24 '18 at 13:22
add a comment |
This is what I ended up with thanks to @Damien_The_Unbeliever and @Mayank Porwal
Select TOP(10) *
FROM
(
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) a
ORDER BY a.PublishDate DESC
That's great. But mark the appropriate responses as answers since that how SO is designed to work - questions are asked and then answered so that others having the same or similar question can find an answer.
– SMor
Nov 24 '18 at 13:22
add a comment |
This is what I ended up with thanks to @Damien_The_Unbeliever and @Mayank Porwal
Select TOP(10) *
FROM
(
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) a
ORDER BY a.PublishDate DESC
This is what I ended up with thanks to @Damien_The_Unbeliever and @Mayank Porwal
Select TOP(10) *
FROM
(
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT News.Author, News.CreateDate, News.CustomerId, News.EditDate, News.EndDate, News.Id, News.ImageUrl, News.PublishDate, News.Text, News.Title
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
) a
ORDER BY a.PublishDate DESC
answered Nov 23 '18 at 9:17
FerronSWFerronSW
207
207
That's great. But mark the appropriate responses as answers since that how SO is designed to work - questions are asked and then answered so that others having the same or similar question can find an answer.
– SMor
Nov 24 '18 at 13:22
add a comment |
That's great. But mark the appropriate responses as answers since that how SO is designed to work - questions are asked and then answered so that others having the same or similar question can find an answer.
– SMor
Nov 24 '18 at 13:22
That's great. But mark the appropriate responses as answers since that how SO is designed to work - questions are asked and then answered so that others having the same or similar question can find an answer.
– SMor
Nov 24 '18 at 13:22
That's great. But mark the appropriate responses as answers since that how SO is designed to work - questions are asked and then answered so that others having the same or similar question can find an answer.
– SMor
Nov 24 '18 at 13:22
add a comment |
this will work:
SELECT TOP 10 * from (SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC);
add a comment |
this will work:
SELECT TOP 10 * from (SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC);
add a comment |
this will work:
SELECT TOP 10 * from (SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC);
this will work:
SELECT TOP 10 * from (SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (1)
EXCEPT
SELECT *
FROM News
INNER JOIN NewsAssignment on News.Id = NewsAssignment.NewsId
INNER JOIN NewsAudience on NewsAssignment.NewsAudienceId = NewsAudience.Id
WHERE NewsAudience.PortalId IN (2)
ORDER BY News.PublishDate DESC);
edited Nov 23 '18 at 10:04
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2562719
1,2562719
add a comment |
add a comment |
Your query doesn't make sense. You are getting all rows from the first subquery. Why? Because you are using select *
and NewsAudience.PortalId
is in the select list. That value is automatically different based on the conditions in the subqueries.
I would recommend phrasing your need differently. For instance, you might intend:
SELECT n.*
FROM News n
WHERE EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (1)
WHERE n.Id = na.NewsId
) AND
NOT EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (2)
WHERE n.Id = na.NewsId
)
ORDER BY n.PublishDate DESC;
You can add TOP (10)
to the outer query if duplicates are still a problem after you have correct the logic.
add a comment |
Your query doesn't make sense. You are getting all rows from the first subquery. Why? Because you are using select *
and NewsAudience.PortalId
is in the select list. That value is automatically different based on the conditions in the subqueries.
I would recommend phrasing your need differently. For instance, you might intend:
SELECT n.*
FROM News n
WHERE EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (1)
WHERE n.Id = na.NewsId
) AND
NOT EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (2)
WHERE n.Id = na.NewsId
)
ORDER BY n.PublishDate DESC;
You can add TOP (10)
to the outer query if duplicates are still a problem after you have correct the logic.
add a comment |
Your query doesn't make sense. You are getting all rows from the first subquery. Why? Because you are using select *
and NewsAudience.PortalId
is in the select list. That value is automatically different based on the conditions in the subqueries.
I would recommend phrasing your need differently. For instance, you might intend:
SELECT n.*
FROM News n
WHERE EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (1)
WHERE n.Id = na.NewsId
) AND
NOT EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (2)
WHERE n.Id = na.NewsId
)
ORDER BY n.PublishDate DESC;
You can add TOP (10)
to the outer query if duplicates are still a problem after you have correct the logic.
Your query doesn't make sense. You are getting all rows from the first subquery. Why? Because you are using select *
and NewsAudience.PortalId
is in the select list. That value is automatically different based on the conditions in the subqueries.
I would recommend phrasing your need differently. For instance, you might intend:
SELECT n.*
FROM News n
WHERE EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (1)
WHERE n.Id = na.NewsId
) AND
NOT EXISTS (SELECT 1
FROM NewsAssignment na JOIN
NewsAudience nau
ON na.NewsAudienceId = nau.Id AND na.PortalId IN (2)
WHERE n.Id = na.NewsId
)
ORDER BY n.PublishDate DESC;
You can add TOP (10)
to the outer query if duplicates are still a problem after you have correct the logic.
answered Nov 23 '18 at 12:33
Gordon LinoffGordon Linoff
761k35294399
761k35294399
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53443397%2fsql-query-except-then-select-top-10%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
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql
,oracle
,sql-server
,db2
, ...– a_horse_with_no_name
Nov 23 '18 at 9:01
1
Since you select all columns, the EXCEPT wont remove anything. NewsAudience.PortalId = 1 for first select, and 2 for second select.
– jarlh
Nov 23 '18 at 9:54