How do I select a column's values only if the values exist in another column in SQL?
I have a slightly complicated question. There are a few columns. Essentially the logic is that there are a bunch of titles. Each title has a unique identifier and these are part of a series. As evident in 'Table Now' there are a few different Goblins: Goblins, Goblins 2 and Goblins 3. These have separate unique identifiers if they are different titles but since they are all Goblins they have a unique series identifier. These titles/creatures can have any home. But Sauron only owns those in Lairs.
What I would like to figure out is the % of what Sauron owns is unique to Sauron versus something else owned by another person. So for example, Vampires is uniquely owned by Sauron as no one else owns it. If a unique series identifier is owned by Sauron then I want it.
The way I have thought about to tackle this is to find all that Sauron owns. Based on the unique series identifier of what Sauron owns I would like to find everything in the table. At the moment there are very few unique series identifier so it is easy to type it manually like unique series identifier = 'Gob' but if there were many many, how would I go about this? I want to find all that Sauron owns and all matches of the unique series identifier where Sauron owns too. So stuff like Monsters and Trolls not being owned by Sauron are not present. And imagining that this table is a million or more rather than a small set of things.
Appreciate any help and sorry for the wordvomit, couldn't think of a better way to explain this! :D
Example table to showcase
sql database amazon-redshift
edited Nov 13 '18 at 18:42
Sami
8,76831241
asked Nov 13 '18 at 18:34
NewbieQuestionsNewbieQuestions
91
add a comment |
I have a slightly complicated question. There are a few columns. Essentially the logic is that there are a bunch of titles. Each title has a unique identifier and these are part of a series. As evident in 'Table Now' there are a few different Goblins: Goblins, Goblins 2 and Goblins 3. These have separate unique identifiers if they are different titles but since they are all Goblins they have a unique series identifier. These titles/creatures can have any home. But Sauron only owns those in Lairs.
What I would like to figure out is the % of what Sauron owns is unique to Sauron versus something else owned by another person. So for example, Vampires is uniquely owned by Sauron as no one else owns it. If a unique series identifier is owned by Sauron then I want it.
The way I have thought about to tackle this is to find all that Sauron owns. Based on the unique series identifier of what Sauron owns I would like to find everything in the table. At the moment there are very few unique series identifier so it is easy to type it manually like unique series identifier = 'Gob' but if there were many many, how would I go about this? I want to find all that Sauron owns and all matches of the unique series identifier where Sauron owns too. So stuff like Monsters and Trolls not being owned by Sauron are not present. And imagining that this table is a million or more rather than a small set of things.
Appreciate any help and sorry for the wordvomit, couldn't think of a better way to explain this! :D
Example table to showcase
sql database amazon-redshift
edited Nov 13 '18 at 18:42
Sami
8,76831241
asked Nov 13 '18 at 18:34
NewbieQuestionsNewbieQuestions
91
add a comment |
I have a slightly complicated question. There are a few columns. Essentially the logic is that there are a bunch of titles. Each title has a unique identifier and these are part of a series. As evident in 'Table Now' there are a few different Goblins: Goblins, Goblins 2 and Goblins 3. These have separate unique identifiers if they are different titles but since they are all Goblins they have a unique series identifier. These titles/creatures can have any home. But Sauron only owns those in Lairs.
What I would like to figure out is the % of what Sauron owns is unique to Sauron versus something else owned by another person. So for example, Vampires is uniquely owned by Sauron as no one else owns it. If a unique series identifier is owned by Sauron then I want it.
The way I have thought about to tackle this is to find all that Sauron owns. Based on the unique series identifier of what Sauron owns I would like to find everything in the table. At the moment there are very few unique series identifier so it is easy to type it manually like unique series identifier = 'Gob' but if there were many many, how would I go about this? I want to find all that Sauron owns and all matches of the unique series identifier where Sauron owns too. So stuff like Monsters and Trolls not being owned by Sauron are not present. And imagining that this table is a million or more rather than a small set of things.
Appreciate any help and sorry for the wordvomit, couldn't think of a better way to explain this! :D
Example table to showcase
sql database amazon-redshift
edited Nov 13 '18 at 18:42
Sami
8,76831241
asked Nov 13 '18 at 18:34
NewbieQuestionsNewbieQuestions
91
I have a slightly complicated question. There are a few columns. Essentially the logic is that there are a bunch of titles. Each title has a unique identifier and these are part of a series. As evident in 'Table Now' there are a few different Goblins: Goblins, Goblins 2 and Goblins 3. These have separate unique identifiers if they are different titles but since they are all Goblins they have a unique series identifier. These titles/creatures can have any home. But Sauron only owns those in Lairs.
What I would like to figure out is the % of what Sauron owns is unique to Sauron versus something else owned by another person. So for example, Vampires is uniquely owned by Sauron as no one else owns it. If a unique series identifier is owned by Sauron then I want it.
The way I have thought about to tackle this is to find all that Sauron owns. Based on the unique series identifier of what Sauron owns I would like to find everything in the table. At the moment there are very few unique series identifier so it is easy to type it manually like unique series identifier = 'Gob' but if there were many many, how would I go about this? I want to find all that Sauron owns and all matches of the unique series identifier where Sauron owns too. So stuff like Monsters and Trolls not being owned by Sauron are not present. And imagining that this table is a million or more rather than a small set of things.
Appreciate any help and sorry for the wordvomit, couldn't think of a better way to explain this! :D
Example table to showcase
sql database amazon-redshift
sql database amazon-redshift
edited Nov 13 '18 at 18:42
Sami
8,76831241
asked Nov 13 '18 at 18:34
NewbieQuestionsNewbieQuestions
91
edited Nov 13 '18 at 18:42
Sami
8,76831241
asked Nov 13 '18 at 18:34
NewbieQuestionsNewbieQuestions
91
edited Nov 13 '18 at 18:42
Sami
8,76831241
edited Nov 13 '18 at 18:42
Sami
8,76831241
edited Nov 13 '18 at 18:42
Sami
8,76831241
8,76831241
asked Nov 13 '18 at 18:34
NewbieQuestionsNewbieQuestions
91
asked Nov 13 '18 at 18:34
NewbieQuestionsNewbieQuestions
91
asked Nov 13 '18 at 18:34
NewbieQuestionsNewbieQuestions
91
91
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You want "title"s that have at least one "Sauron". If so, you can use window functions:
select t.*
from (select t.*,
sum(case when evillord = 'Sauron' then 1 else 0 end) over (partition by title) as num_saurons
from t
) t
where num_saurons > 0;
answered Nov 13 '18 at 18:49
Gordon LinoffGordon Linoff
769k35300402
add a comment |
Select all unique series identifiers for Sauron. Then select all titles with those unique series identifiers.
select * from mytable
where unique_series_identifier in
(
select unique_series_identifier
from mytable
where evil_lord = 'Sauron'
)
order by unique_series_identifier, unique_identifier;
I suggest to have the following indexes for this query. One to find Saurons titles, one to find titles by unique series identifier.
create index idx1 on mytable ( evil_lord, unique_series_identifier );
create index idx2 on mytable ( unique_series_identifier );
answered Nov 13 '18 at 18:56
Thorsten KettnerThorsten Kettner
51.5k32642
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%2f53287458%2fhow-do-i-select-a-columns-values-only-if-the-values-exist-in-another-column-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You want "title"s that have at least one "Sauron". If so, you can use window functions:
select t.*
from (select t.*,
sum(case when evillord = 'Sauron' then 1 else 0 end) over (partition by title) as num_saurons
from t
) t
where num_saurons > 0;
answered Nov 13 '18 at 18:49
Gordon LinoffGordon Linoff
769k35300402
add a comment |
You want "title"s that have at least one "Sauron". If so, you can use window functions:
select t.*
from (select t.*,
sum(case when evillord = 'Sauron' then 1 else 0 end) over (partition by title) as num_saurons
from t
) t
where num_saurons > 0;
answered Nov 13 '18 at 18:49
Gordon LinoffGordon Linoff
769k35300402
add a comment |
You want "title"s that have at least one "Sauron". If so, you can use window functions:
select t.*
from (select t.*,
sum(case when evillord = 'Sauron' then 1 else 0 end) over (partition by title) as num_saurons
from t
) t
where num_saurons > 0;
answered Nov 13 '18 at 18:49
Gordon LinoffGordon Linoff
769k35300402
You want "title"s that have at least one "Sauron". If so, you can use window functions:
select t.*
from (select t.*,
sum(case when evillord = 'Sauron' then 1 else 0 end) over (partition by title) as num_saurons
from t
) t
where num_saurons > 0;
answered Nov 13 '18 at 18:49
Gordon LinoffGordon Linoff
769k35300402
answered Nov 13 '18 at 18:49
Gordon LinoffGordon Linoff
769k35300402
answered Nov 13 '18 at 18:49
Gordon LinoffGordon Linoff
769k35300402
answered Nov 13 '18 at 18:49
Gordon LinoffGordon Linoff
769k35300402
769k35300402
add a comment |
add a comment |
Select all unique series identifiers for Sauron. Then select all titles with those unique series identifiers.
select * from mytable
where unique_series_identifier in
(
select unique_series_identifier
from mytable
where evil_lord = 'Sauron'
)
order by unique_series_identifier, unique_identifier;
I suggest to have the following indexes for this query. One to find Saurons titles, one to find titles by unique series identifier.
create index idx1 on mytable ( evil_lord, unique_series_identifier );
create index idx2 on mytable ( unique_series_identifier );
answered Nov 13 '18 at 18:56
Thorsten KettnerThorsten Kettner
51.5k32642
add a comment |
Select all unique series identifiers for Sauron. Then select all titles with those unique series identifiers.
select * from mytable
where unique_series_identifier in
(
select unique_series_identifier
from mytable
where evil_lord = 'Sauron'
)
order by unique_series_identifier, unique_identifier;
I suggest to have the following indexes for this query. One to find Saurons titles, one to find titles by unique series identifier.
create index idx1 on mytable ( evil_lord, unique_series_identifier );
create index idx2 on mytable ( unique_series_identifier );
answered Nov 13 '18 at 18:56
Thorsten KettnerThorsten Kettner
51.5k32642
add a comment |
Select all unique series identifiers for Sauron. Then select all titles with those unique series identifiers.
select * from mytable
where unique_series_identifier in
(
select unique_series_identifier
from mytable
where evil_lord = 'Sauron'
)
order by unique_series_identifier, unique_identifier;
I suggest to have the following indexes for this query. One to find Saurons titles, one to find titles by unique series identifier.
create index idx1 on mytable ( evil_lord, unique_series_identifier );
create index idx2 on mytable ( unique_series_identifier );
answered Nov 13 '18 at 18:56
Thorsten KettnerThorsten Kettner
51.5k32642
Select all unique series identifiers for Sauron. Then select all titles with those unique series identifiers.
select * from mytable
where unique_series_identifier in
(
select unique_series_identifier
from mytable
where evil_lord = 'Sauron'
)
order by unique_series_identifier, unique_identifier;
I suggest to have the following indexes for this query. One to find Saurons titles, one to find titles by unique series identifier.
create index idx1 on mytable ( evil_lord, unique_series_identifier );
create index idx2 on mytable ( unique_series_identifier );
answered Nov 13 '18 at 18:56
Thorsten KettnerThorsten Kettner
51.5k32642
answered Nov 13 '18 at 18:56
Thorsten KettnerThorsten Kettner
51.5k32642
answered Nov 13 '18 at 18:56
Thorsten KettnerThorsten Kettner
51.5k32642
answered Nov 13 '18 at 18:56
Thorsten KettnerThorsten Kettner
51.5k32642
51.5k32642
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%2f53287458%2fhow-do-i-select-a-columns-values-only-if-the-values-exist-in-another-column-in%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
