Mixing
Exclude related records - guilt by association
I have this MySQL query:
select *
from Movies_Genres
where MovieID = 271
That returns the following recordset:
ID MovieID GenreID
======================
924 271 8
1251 271 13
Movies_Genres is a union/junction table with many-many relationships.
Now if I run this slightly modified query:
select *
from Movies_Genres
where MovieID = 271
and GenreID <> 13
I get the following recordset:
ID MovieID GenreID
======================
924 271 8
So far so good. But what I'm trying to achieve is to have 0 records returned for that 2nd query if the movie has a related GenreID of 13. So, the fact that in my query above the movie has related genres of 13 AND 8, I want 0 records returned because the movie has a GenreID of 13. If it had a GenreID of, say, 8, 1, and 2 - then I would want 3 records returned. If it had a GenreID of only 13, I would want 0 records returned -- which it already does correctly. To summarize: Any movie with a related GenreID of 13 should never return any records, even if it also has other linked GenreIDs.
How can this be achieved?
mysql sql
asked Jan 2 at 14:05
HerrimanCoderHerrimanCoder
1,676164683
add a comment |
I have this MySQL query:
select *
from Movies_Genres
where MovieID = 271
That returns the following recordset:
ID MovieID GenreID
======================
924 271 8
1251 271 13
Movies_Genres is a union/junction table with many-many relationships.
Now if I run this slightly modified query:
select *
from Movies_Genres
where MovieID = 271
and GenreID <> 13
I get the following recordset:
ID MovieID GenreID
======================
924 271 8
So far so good. But what I'm trying to achieve is to have 0 records returned for that 2nd query if the movie has a related GenreID of 13. So, the fact that in my query above the movie has related genres of 13 AND 8, I want 0 records returned because the movie has a GenreID of 13. If it had a GenreID of, say, 8, 1, and 2 - then I would want 3 records returned. If it had a GenreID of only 13, I would want 0 records returned -- which it already does correctly. To summarize: Any movie with a related GenreID of 13 should never return any records, even if it also has other linked GenreIDs.
How can this be achieved?
mysql sql
asked Jan 2 at 14:05
HerrimanCoderHerrimanCoder
1,676164683
add a comment |
I have this MySQL query:
select *
from Movies_Genres
where MovieID = 271
That returns the following recordset:
ID MovieID GenreID
======================
924 271 8
1251 271 13
Movies_Genres is a union/junction table with many-many relationships.
Now if I run this slightly modified query:
select *
from Movies_Genres
where MovieID = 271
and GenreID <> 13
I get the following recordset:
ID MovieID GenreID
======================
924 271 8
So far so good. But what I'm trying to achieve is to have 0 records returned for that 2nd query if the movie has a related GenreID of 13. So, the fact that in my query above the movie has related genres of 13 AND 8, I want 0 records returned because the movie has a GenreID of 13. If it had a GenreID of, say, 8, 1, and 2 - then I would want 3 records returned. If it had a GenreID of only 13, I would want 0 records returned -- which it already does correctly. To summarize: Any movie with a related GenreID of 13 should never return any records, even if it also has other linked GenreIDs.
How can this be achieved?
mysql sql
asked Jan 2 at 14:05
HerrimanCoderHerrimanCoder
1,676164683
I have this MySQL query:
select *
from Movies_Genres
where MovieID = 271
That returns the following recordset:
ID MovieID GenreID
======================
924 271 8
1251 271 13
Movies_Genres is a union/junction table with many-many relationships.
Now if I run this slightly modified query:
select *
from Movies_Genres
where MovieID = 271
and GenreID <> 13
I get the following recordset:
ID MovieID GenreID
======================
924 271 8
So far so good. But what I'm trying to achieve is to have 0 records returned for that 2nd query if the movie has a related GenreID of 13. So, the fact that in my query above the movie has related genres of 13 AND 8, I want 0 records returned because the movie has a GenreID of 13. If it had a GenreID of, say, 8, 1, and 2 - then I would want 3 records returned. If it had a GenreID of only 13, I would want 0 records returned -- which it already does correctly. To summarize: Any movie with a related GenreID of 13 should never return any records, even if it also has other linked GenreIDs.
How can this be achieved?
mysql sql
mysql sql
asked Jan 2 at 14:05
HerrimanCoderHerrimanCoder
1,676164683
asked Jan 2 at 14:05
HerrimanCoderHerrimanCoder
1,676164683
asked Jan 2 at 14:05
HerrimanCoderHerrimanCoder
1,676164683
asked Jan 2 at 14:05
HerrimanCoderHerrimanCoder
1,676164683
asked Jan 2 at 14:05
HerrimanCoderHerrimanCoder
1,676164683
1,676164683
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
If you want the original records, then not exists seems appropriate:
select mg.*
from Movies_Genres mg
where not exists (select 1
from movies_genres mg2
where mg2.MovieID = mg.MovieID and
mg2.GenreID = 13
);
If you just want the movies rather than the genreID details, you can use group by:
select mg.MovieID
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
In fact, you can add the genre ids as a list in this case:
select mg.MovieID, group_concat(mg.GenreID) as genreids
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
answered Jan 2 at 14:11
Gordon LinoffGordon Linoff
790k35314418
having sum( mg2.GenreID = 13 )will assigning genreId to 13 work even i tried the same way similar to what count(..)
– Himanshu Ahuja
Jan 2 at 14:20
@HimanshuAhuja . . . Huh? I really don't understand your comment. The expressionsum( mg.GenreID = 13 )counts the number of times thatgenreIdis 13. The= 0says there are no such rows for the movie.
– Gordon Linoff
Jan 2 at 15:00
thats what i said the thing is this a correct way of making aggregation work to avoid writing the same column in group by
– Himanshu Ahuja
Jan 2 at 16:07
This seems really promising but I cannot add my order by:ORDER BY RAND()- it throws an error on theorder by
– HerrimanCoder
Jan 3 at 21:02
@HerrimanCoder . . . Why can't you addorder by rand()? That should go right after thehavingclause.
– Gordon Linoff
Jan 3 at 22:25
|
show 1 more comment
One option involves aggregation:
SELECT MovieID
FROM Movies_Genres
WHERE MovieID = 271
GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) = 0;
The above query is somewhat trivial, because it would at most return a single MovieID, 271, if it did not have the 13 genre associated with it. If you want to find the full records, we can try:
SELECT mg.*
FROM Movies_Genres mg
WHERE mg.MovieID NOT IN (SELECT MovieID FROM Movies_Genres GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) > 0);
answered Jan 2 at 14:09
Tim BiegeleisenTim Biegeleisen
234k13100158
add a comment |
Perhaps you need a group by with having
select *
from Movies_Genres
group by MovieId having count(GenreID=13) =0
answered Jan 2 at 14:09
Himanshu AhujaHimanshu Ahuja
9322218
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%2f54007745%2fexclude-related-records-guilt-by-association%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you want the original records, then not exists seems appropriate:
select mg.*
from Movies_Genres mg
where not exists (select 1
from movies_genres mg2
where mg2.MovieID = mg.MovieID and
mg2.GenreID = 13
);
If you just want the movies rather than the genreID details, you can use group by:
select mg.MovieID
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
In fact, you can add the genre ids as a list in this case:
select mg.MovieID, group_concat(mg.GenreID) as genreids
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
answered Jan 2 at 14:11
Gordon LinoffGordon Linoff
790k35314418
having sum( mg2.GenreID = 13 )will assigning genreId to 13 work even i tried the same way similar to what count(..)
– Himanshu Ahuja
Jan 2 at 14:20
@HimanshuAhuja . . . Huh? I really don't understand your comment. The expressionsum( mg.GenreID = 13 )counts the number of times thatgenreIdis 13. The= 0says there are no such rows for the movie.
– Gordon Linoff
Jan 2 at 15:00
thats what i said the thing is this a correct way of making aggregation work to avoid writing the same column in group by
– Himanshu Ahuja
Jan 2 at 16:07
This seems really promising but I cannot add my order by:ORDER BY RAND()- it throws an error on theorder by
– HerrimanCoder
Jan 3 at 21:02
@HerrimanCoder . . . Why can't you addorder by rand()? That should go right after thehavingclause.
– Gordon Linoff
Jan 3 at 22:25
|
show 1 more comment
If you want the original records, then not exists seems appropriate:
select mg.*
from Movies_Genres mg
where not exists (select 1
from movies_genres mg2
where mg2.MovieID = mg.MovieID and
mg2.GenreID = 13
);
If you just want the movies rather than the genreID details, you can use group by:
select mg.MovieID
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
In fact, you can add the genre ids as a list in this case:
select mg.MovieID, group_concat(mg.GenreID) as genreids
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
answered Jan 2 at 14:11
Gordon LinoffGordon Linoff
790k35314418
having sum( mg2.GenreID = 13 )will assigning genreId to 13 work even i tried the same way similar to what count(..)
– Himanshu Ahuja
Jan 2 at 14:20
@HimanshuAhuja . . . Huh? I really don't understand your comment. The expressionsum( mg.GenreID = 13 )counts the number of times thatgenreIdis 13. The= 0says there are no such rows for the movie.
– Gordon Linoff
Jan 2 at 15:00
thats what i said the thing is this a correct way of making aggregation work to avoid writing the same column in group by
– Himanshu Ahuja
Jan 2 at 16:07
This seems really promising but I cannot add my order by:ORDER BY RAND()- it throws an error on theorder by
– HerrimanCoder
Jan 3 at 21:02
@HerrimanCoder . . . Why can't you addorder by rand()? That should go right after thehavingclause.
– Gordon Linoff
Jan 3 at 22:25
|
show 1 more comment
If you want the original records, then not exists seems appropriate:
select mg.*
from Movies_Genres mg
where not exists (select 1
from movies_genres mg2
where mg2.MovieID = mg.MovieID and
mg2.GenreID = 13
);
If you just want the movies rather than the genreID details, you can use group by:
select mg.MovieID
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
In fact, you can add the genre ids as a list in this case:
select mg.MovieID, group_concat(mg.GenreID) as genreids
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
answered Jan 2 at 14:11
Gordon LinoffGordon Linoff
790k35314418
If you want the original records, then not exists seems appropriate:
select mg.*
from Movies_Genres mg
where not exists (select 1
from movies_genres mg2
where mg2.MovieID = mg.MovieID and
mg2.GenreID = 13
);
If you just want the movies rather than the genreID details, you can use group by:
select mg.MovieID
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
In fact, you can add the genre ids as a list in this case:
select mg.MovieID, group_concat(mg.GenreID) as genreids
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;
answered Jan 2 at 14:11
Gordon LinoffGordon Linoff
790k35314418
answered Jan 2 at 14:11
Gordon LinoffGordon Linoff
790k35314418
answered Jan 2 at 14:11
Gordon LinoffGordon Linoff
790k35314418
answered Jan 2 at 14:11
Gordon LinoffGordon Linoff
790k35314418
790k35314418
having sum( mg2.GenreID = 13 )will assigning genreId to 13 work even i tried the same way similar to what count(..)
– Himanshu Ahuja
Jan 2 at 14:20
@HimanshuAhuja . . . Huh? I really don't understand your comment. The expressionsum( mg.GenreID = 13 )counts the number of times thatgenreIdis 13. The= 0says there are no such rows for the movie.
– Gordon Linoff
Jan 2 at 15:00
thats what i said the thing is this a correct way of making aggregation work to avoid writing the same column in group by
– Himanshu Ahuja
Jan 2 at 16:07
This seems really promising but I cannot add my order by:ORDER BY RAND()- it throws an error on theorder by
– HerrimanCoder
Jan 3 at 21:02
@HerrimanCoder . . . Why can't you addorder by rand()? That should go right after thehavingclause.
– Gordon Linoff
Jan 3 at 22:25
|
show 1 more comment
having sum( mg2.GenreID = 13 )will assigning genreId to 13 work even i tried the same way similar to what count(..)
– Himanshu Ahuja
Jan 2 at 14:20
@HimanshuAhuja . . . Huh? I really don't understand your comment. The expressionsum( mg.GenreID = 13 )counts the number of times thatgenreIdis 13. The= 0says there are no such rows for the movie.
– Gordon Linoff
Jan 2 at 15:00
thats what i said the thing is this a correct way of making aggregation work to avoid writing the same column in group by
– Himanshu Ahuja
Jan 2 at 16:07
This seems really promising but I cannot add my order by:ORDER BY RAND()- it throws an error on theorder by
– HerrimanCoder
Jan 3 at 21:02
@HerrimanCoder . . . Why can't you addorder by rand()? That should go right after thehavingclause.
– Gordon Linoff
Jan 3 at 22:25
having sum( mg2.GenreID = 13 ) will assigning genreId to 13 work even i tried the same way similar to what count(..)– Himanshu Ahuja
Jan 2 at 14:20
having sum( mg2.GenreID = 13 ) will assigning genreId to 13 work even i tried the same way similar to what count(..)– Himanshu Ahuja
Jan 2 at 14:20
@HimanshuAhuja . . . Huh? I really don't understand your comment. The expression
sum( mg.GenreID = 13 ) counts the number of times that genreId is 13. The = 0 says there are no such rows for the movie.– Gordon Linoff
Jan 2 at 15:00
@HimanshuAhuja . . . Huh? I really don't understand your comment. The expression
sum( mg.GenreID = 13 ) counts the number of times that genreId is 13. The = 0 says there are no such rows for the movie.– Gordon Linoff
Jan 2 at 15:00
thats what i said the thing is this a correct way of making aggregation work to avoid writing the same column in group by
– Himanshu Ahuja
Jan 2 at 16:07
thats what i said the thing is this a correct way of making aggregation work to avoid writing the same column in group by
– Himanshu Ahuja
Jan 2 at 16:07
This seems really promising but I cannot add my order by:
ORDER BY RAND() - it throws an error on the order by– HerrimanCoder
Jan 3 at 21:02
This seems really promising but I cannot add my order by:
ORDER BY RAND() - it throws an error on the order by– HerrimanCoder
Jan 3 at 21:02
@HerrimanCoder . . . Why can't you add
order by rand()? That should go right after the having clause.– Gordon Linoff
Jan 3 at 22:25
@HerrimanCoder . . . Why can't you add
order by rand()? That should go right after the having clause.– Gordon Linoff
Jan 3 at 22:25
|
show 1 more comment
One option involves aggregation:
SELECT MovieID
FROM Movies_Genres
WHERE MovieID = 271
GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) = 0;
The above query is somewhat trivial, because it would at most return a single MovieID, 271, if it did not have the 13 genre associated with it. If you want to find the full records, we can try:
SELECT mg.*
FROM Movies_Genres mg
WHERE mg.MovieID NOT IN (SELECT MovieID FROM Movies_Genres GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) > 0);
answered Jan 2 at 14:09
Tim BiegeleisenTim Biegeleisen
234k13100158
add a comment |
One option involves aggregation:
SELECT MovieID
FROM Movies_Genres
WHERE MovieID = 271
GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) = 0;
The above query is somewhat trivial, because it would at most return a single MovieID, 271, if it did not have the 13 genre associated with it. If you want to find the full records, we can try:
SELECT mg.*
FROM Movies_Genres mg
WHERE mg.MovieID NOT IN (SELECT MovieID FROM Movies_Genres GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) > 0);
answered Jan 2 at 14:09
Tim BiegeleisenTim Biegeleisen
234k13100158
add a comment |
One option involves aggregation:
SELECT MovieID
FROM Movies_Genres
WHERE MovieID = 271
GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) = 0;
The above query is somewhat trivial, because it would at most return a single MovieID, 271, if it did not have the 13 genre associated with it. If you want to find the full records, we can try:
SELECT mg.*
FROM Movies_Genres mg
WHERE mg.MovieID NOT IN (SELECT MovieID FROM Movies_Genres GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) > 0);
answered Jan 2 at 14:09
Tim BiegeleisenTim Biegeleisen
234k13100158
One option involves aggregation:
SELECT MovieID
FROM Movies_Genres
WHERE MovieID = 271
GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) = 0;
The above query is somewhat trivial, because it would at most return a single MovieID, 271, if it did not have the 13 genre associated with it. If you want to find the full records, we can try:
SELECT mg.*
FROM Movies_Genres mg
WHERE mg.MovieID NOT IN (SELECT MovieID FROM Movies_Genres GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) > 0);
answered Jan 2 at 14:09
Tim BiegeleisenTim Biegeleisen
234k13100158
answered Jan 2 at 14:09
Tim BiegeleisenTim Biegeleisen
234k13100158
answered Jan 2 at 14:09
Tim BiegeleisenTim Biegeleisen
234k13100158
answered Jan 2 at 14:09
Tim BiegeleisenTim Biegeleisen
234k13100158
234k13100158
add a comment |
add a comment |
Perhaps you need a group by with having
select *
from Movies_Genres
group by MovieId having count(GenreID=13) =0
answered Jan 2 at 14:09
Himanshu AhujaHimanshu Ahuja
9322218
add a comment |
Perhaps you need a group by with having
select *
from Movies_Genres
group by MovieId having count(GenreID=13) =0
answered Jan 2 at 14:09
Himanshu AhujaHimanshu Ahuja
9322218
add a comment |
Perhaps you need a group by with having
select *
from Movies_Genres
group by MovieId having count(GenreID=13) =0
answered Jan 2 at 14:09
Himanshu AhujaHimanshu Ahuja
9322218
Perhaps you need a group by with having
select *
from Movies_Genres
group by MovieId having count(GenreID=13) =0
answered Jan 2 at 14:09
Himanshu AhujaHimanshu Ahuja
9322218
edited Jan 2 at 14:18
answered Jan 2 at 14:09
Himanshu AhujaHimanshu Ahuja
9322218
answered Jan 2 at 14:09
Himanshu AhujaHimanshu Ahuja
9322218
answered Jan 2 at 14:09
Himanshu AhujaHimanshu Ahuja
9322218
9322218
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.
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%2f54007745%2fexclude-related-records-guilt-by-association%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
