SQL Query Except then select TOP 10
-1
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 edited Nov 23 '18 at 9:52
jarlh
29k52138
asked Nov 23 '18 at 8:56
FerronSWFerronSW
207
add a comment |
-1
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 edited Nov 23 '18 at 9:52
jarlh
29k52138
asked Nov 23 '18 at 8:56
FerronSWFerronSW
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 |
-1
-1
-1
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 edited Nov 23 '18 at 9:52
jarlh
29k52138
asked Nov 23 '18 at 8:56
FerronSWFerronSW
207
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
29k52138
asked Nov 23 '18 at 8:56
FerronSWFerronSW
207
edited Nov 23 '18 at 9:52
jarlh
29k52138
asked Nov 23 '18 at 8:56
FerronSWFerronSW
207
edited Nov 23 '18 at 9:52
jarlh
29k52138
edited Nov 23 '18 at 9:52
jarlh
29k52138
edited Nov 23 '18 at 9:52
jarlh
29k52138
29k52138
asked Nov 23 '18 at 8:56
FerronSWFerronSW
207
asked Nov 23 '18 at 8:56
FerronSWFerronSW
207
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
0
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,8952724
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 |
0
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
12.7k2932
add a comment |
0
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
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 |
0
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);
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2762719
add a comment |
0
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
769k35303404
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%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
0
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,8952724
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 |
0
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,8952724
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 |
0
0
0
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,8952724
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,8952724
answered Nov 23 '18 at 9:00
Mayank PorwalMayank Porwal
4,8952724
answered Nov 23 '18 at 9:00
Mayank PorwalMayank Porwal
4,8952724
answered Nov 23 '18 at 9:00
Mayank PorwalMayank Porwal
4,8952724
4,8952724
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 |
0
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
12.7k2932
add a comment |
0
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
12.7k2932
add a comment |
0
0
0
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
12.7k2932
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
12.7k2932
answered Nov 23 '18 at 9:14
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.7k2932
answered Nov 23 '18 at 9:14
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.7k2932
answered Nov 23 '18 at 9:14
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.7k2932
12.7k2932
add a comment |
add a comment |
0
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
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 |
0
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
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 |
0
0
0
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
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
answered Nov 23 '18 at 9:17
FerronSWFerronSW
207
answered Nov 23 '18 at 9:17
FerronSWFerronSW
207
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 |
0
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);
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2762719
add a comment |
0
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);
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2762719
add a comment |
0
0
0
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);
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2762719
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);
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2762719
edited Nov 23 '18 at 10:04
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2762719
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2762719
answered Nov 23 '18 at 9:54
nikhil sugandhnikhil sugandh
1,2762719
1,2762719
add a comment |
add a comment |
0
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
769k35303404
add a comment |
0
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
769k35303404
add a comment |
0
0
0
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
769k35303404
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
769k35303404
answered Nov 23 '18 at 12:33
Gordon LinoffGordon Linoff
769k35303404
answered Nov 23 '18 at 12:33
Gordon LinoffGordon Linoff
769k35303404
answered Nov 23 '18 at 12:33
Gordon LinoffGordon Linoff
769k35303404
769k35303404
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%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