SQL Server - Is there a better way for me to select based on values in varying numbers of rows? The Next CEO of Stack OverflowAdd a column with a default value to an existing table in SQL ServerWhat is the best way to paginate results in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?SQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?How to select only the first rows for each unique value of a column'IF' in 'SELECT' statement - choose output value based on column valuesSQL select only rows with max value on a columnSQL: Group by minimum value in one field while selecting distinct rows
Would a completely good Muggle be able to use a wand?
Running a General Election and the European Elections together
What steps are necessary to read a Modern SSD in Medieval Europe?
Why isn't acceleration always zero whenever velocity is zero, such as the moment a ball bounces off a wall?
What does "Its cash flow is deeply negative" mean?
What happened in Rome, when the western empire "fell"?
Is it convenient to ask the journal's editor for two additional days to complete a review?
Axiom Schema vs Axiom
Is there always a complete, orthogonal set of unitary matrices?
Should I tutor a student who I know has cheated on their homework?
Why do airplanes bank sharply to the right after air-to-air refueling?
Grabbing quick drinks
Easy to read palindrome checker
Solving system of ODEs with extra parameter
Is it okay to majorly distort historical facts while writing a fiction story?
Does Germany produce more waste than the US?
How to invert MapIndexed on a ragged structure? How to construct a tree from rules?
TikZ: How to reverse arrow direction without switching start/end point?
Why do remote US companies require working in the US?
Why, when going from special to general relativity, do we just replace partial derivatives with covariant derivatives?
Is it ever safe to open a suspicious HTML file (e.g. email attachment)?
What connection does MS Office have to Netscape Navigator?
RigExpert AA-35 - Interpreting The Information
Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?
SQL Server - Is there a better way for me to select based on values in varying numbers of rows?
The Next CEO of Stack OverflowAdd a column with a default value to an existing table in SQL ServerWhat is the best way to paginate results in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?SQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?How to select only the first rows for each unique value of a column'IF' in 'SELECT' statement - choose output value based on column valuesSQL select only rows with max value on a columnSQL: Group by minimum value in one field while selecting distinct rows
Suppose I have a table like this:
id value
1 a
1 x
1 x
2 b
2 b
2 y
3 c
3 d
3 z
10 c
10 c
10 z
11 x
11 y
11 z
I want to select id's (duplicate id's are okay) where the value is a, b, or both c and d. So in this case, I would select 1, 2, and 3. Note that duplicate values are possible for each id-value combination.
I have this code that works:
SELECT id
FROM table
WHERE value = 'a' OR value = 'b'
UNION
SELECT t1.id
FROM table t1
INNER JOIN table t2
ON t1.id = t2.id
WHERE t1.value = 'c' AND t2.value = 'd'
But my SQL skills are rusty and I can't shake the feeling that there's a better way to do this. This is going to be a subquery in the middle of some complex legacy code, so I want to be sure that my code is concise and efficient.
Also this question is not a duplicate because it deals with selecting id's based on one row value or two row values at the same time, all the questions I found dealt with selecting based on two row values. I've already solved that issue with the second half of my query.
sql
sql-server asked Mar 7 at 16:49
krockkrock
737
add a comment |
Suppose I have a table like this:
id value
1 a
1 x
1 x
2 b
2 b
2 y
3 c
3 d
3 z
10 c
10 c
10 z
11 x
11 y
11 z
I want to select id's (duplicate id's are okay) where the value is a, b, or both c and d. So in this case, I would select 1, 2, and 3. Note that duplicate values are possible for each id-value combination.
I have this code that works:
SELECT id
FROM table
WHERE value = 'a' OR value = 'b'
UNION
SELECT t1.id
FROM table t1
INNER JOIN table t2
ON t1.id = t2.id
WHERE t1.value = 'c' AND t2.value = 'd'
But my SQL skills are rusty and I can't shake the feeling that there's a better way to do this. This is going to be a subquery in the middle of some complex legacy code, so I want to be sure that my code is concise and efficient.
Also this question is not a duplicate because it deals with selecting id's based on one row value or two row values at the same time, all the questions I found dealt with selecting based on two row values. I've already solved that issue with the second half of my query.
sql
sql-server asked Mar 7 at 16:49
krockkrock
737
@PM77-1 care to elaborate? I'm not seeing how that would work
– krock
Mar 7 at 17:03
I misunderstood your task.
– PM 77-1
Mar 7 at 17:06
ah no worries, thanks anyway
– krock
Mar 7 at 17:16
add a comment |
Suppose I have a table like this:
id value
1 a
1 x
1 x
2 b
2 b
2 y
3 c
3 d
3 z
10 c
10 c
10 z
11 x
11 y
11 z
I want to select id's (duplicate id's are okay) where the value is a, b, or both c and d. So in this case, I would select 1, 2, and 3. Note that duplicate values are possible for each id-value combination.
I have this code that works:
SELECT id
FROM table
WHERE value = 'a' OR value = 'b'
UNION
SELECT t1.id
FROM table t1
INNER JOIN table t2
ON t1.id = t2.id
WHERE t1.value = 'c' AND t2.value = 'd'
But my SQL skills are rusty and I can't shake the feeling that there's a better way to do this. This is going to be a subquery in the middle of some complex legacy code, so I want to be sure that my code is concise and efficient.
Also this question is not a duplicate because it deals with selecting id's based on one row value or two row values at the same time, all the questions I found dealt with selecting based on two row values. I've already solved that issue with the second half of my query.
sql
sql-server asked Mar 7 at 16:49
krockkrock
737
Suppose I have a table like this:
id value
1 a
1 x
1 x
2 b
2 b
2 y
3 c
3 d
3 z
10 c
10 c
10 z
11 x
11 y
11 z
I want to select id's (duplicate id's are okay) where the value is a, b, or both c and d. So in this case, I would select 1, 2, and 3. Note that duplicate values are possible for each id-value combination.
I have this code that works:
SELECT id
FROM table
WHERE value = 'a' OR value = 'b'
UNION
SELECT t1.id
FROM table t1
INNER JOIN table t2
ON t1.id = t2.id
WHERE t1.value = 'c' AND t2.value = 'd'
But my SQL skills are rusty and I can't shake the feeling that there's a better way to do this. This is going to be a subquery in the middle of some complex legacy code, so I want to be sure that my code is concise and efficient.
Also this question is not a duplicate because it deals with selecting id's based on one row value or two row values at the same time, all the questions I found dealt with selecting based on two row values. I've already solved that issue with the second half of my query.
sql
sql-server sql
sql-server asked Mar 7 at 16:49
krockkrock
737
asked Mar 7 at 16:49
krockkrock
737
asked Mar 7 at 16:49
krockkrock
737
asked Mar 7 at 16:49
krockkrock
737
asked Mar 7 at 16:49
krockkrock
737
737
@PM77-1 care to elaborate? I'm not seeing how that would work
– krock
Mar 7 at 17:03
I misunderstood your task.
– PM 77-1
Mar 7 at 17:06
ah no worries, thanks anyway
– krock
Mar 7 at 17:16
add a comment |
@PM77-1 care to elaborate? I'm not seeing how that would work
– krock
Mar 7 at 17:03
I misunderstood your task.
– PM 77-1
Mar 7 at 17:06
ah no worries, thanks anyway
– krock
Mar 7 at 17:16
@PM77-1 care to elaborate? I'm not seeing how that would work
– krock
Mar 7 at 17:03
@PM77-1 care to elaborate? I'm not seeing how that would work
– krock
Mar 7 at 17:03
I misunderstood your task.
– PM 77-1
Mar 7 at 17:06
I misunderstood your task.
– PM 77-1
Mar 7 at 17:06
ah no worries, thanks anyway
– krock
Mar 7 at 17:16
ah no worries, thanks anyway
– krock
Mar 7 at 17:16
add a comment |
1 Answer
1
active
oldest
votes
A HAVINGclause with some conditional aggregates will do the trick here:
SELECT id
FROM (VALUES(1 ,'a'),
(1 ,'x'),
(1 ,'x'),
(2 ,'b'),
(2 ,'b'),
(2 ,'y'),
(3 ,'c'),
(3 ,'d'),
(3 ,'z'),
(10,'c'),
(10,'c'),
(10,'z'),
(11,'x'),
(11,'y'),
(11,'z')) V(id, [value])
GROUP BY id
HAVING COUNT(CASE [value] WHEN 'a' THEN 1 WHEN 'b' THEN 1 END) > 0
OR (COUNT(CASE [value] WHEN 'c' THEN 1 END) > 0
AND COUNT(CASE [value] WHEN 'd' THEN 1 END) > 0);
answered Mar 7 at 16:53
LarnuLarnu
22.3k51833
ahh I knew there was something simple like this that was I missing. Just what I was looking for, thanks!
– krock
Mar 7 at 17:02
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%2f55048968%2fsql-server-is-there-a-better-way-for-me-to-select-based-on-values-in-varying-n%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
A HAVINGclause with some conditional aggregates will do the trick here:
SELECT id
FROM (VALUES(1 ,'a'),
(1 ,'x'),
(1 ,'x'),
(2 ,'b'),
(2 ,'b'),
(2 ,'y'),
(3 ,'c'),
(3 ,'d'),
(3 ,'z'),
(10,'c'),
(10,'c'),
(10,'z'),
(11,'x'),
(11,'y'),
(11,'z')) V(id, [value])
GROUP BY id
HAVING COUNT(CASE [value] WHEN 'a' THEN 1 WHEN 'b' THEN 1 END) > 0
OR (COUNT(CASE [value] WHEN 'c' THEN 1 END) > 0
AND COUNT(CASE [value] WHEN 'd' THEN 1 END) > 0);
answered Mar 7 at 16:53
LarnuLarnu
22.3k51833
ahh I knew there was something simple like this that was I missing. Just what I was looking for, thanks!
– krock
Mar 7 at 17:02
add a comment |
A HAVINGclause with some conditional aggregates will do the trick here:
SELECT id
FROM (VALUES(1 ,'a'),
(1 ,'x'),
(1 ,'x'),
(2 ,'b'),
(2 ,'b'),
(2 ,'y'),
(3 ,'c'),
(3 ,'d'),
(3 ,'z'),
(10,'c'),
(10,'c'),
(10,'z'),
(11,'x'),
(11,'y'),
(11,'z')) V(id, [value])
GROUP BY id
HAVING COUNT(CASE [value] WHEN 'a' THEN 1 WHEN 'b' THEN 1 END) > 0
OR (COUNT(CASE [value] WHEN 'c' THEN 1 END) > 0
AND COUNT(CASE [value] WHEN 'd' THEN 1 END) > 0);
answered Mar 7 at 16:53
LarnuLarnu
22.3k51833
ahh I knew there was something simple like this that was I missing. Just what I was looking for, thanks!
– krock
Mar 7 at 17:02
add a comment |
A HAVINGclause with some conditional aggregates will do the trick here:
SELECT id
FROM (VALUES(1 ,'a'),
(1 ,'x'),
(1 ,'x'),
(2 ,'b'),
(2 ,'b'),
(2 ,'y'),
(3 ,'c'),
(3 ,'d'),
(3 ,'z'),
(10,'c'),
(10,'c'),
(10,'z'),
(11,'x'),
(11,'y'),
(11,'z')) V(id, [value])
GROUP BY id
HAVING COUNT(CASE [value] WHEN 'a' THEN 1 WHEN 'b' THEN 1 END) > 0
OR (COUNT(CASE [value] WHEN 'c' THEN 1 END) > 0
AND COUNT(CASE [value] WHEN 'd' THEN 1 END) > 0);
answered Mar 7 at 16:53
LarnuLarnu
22.3k51833
A HAVINGclause with some conditional aggregates will do the trick here:
SELECT id
FROM (VALUES(1 ,'a'),
(1 ,'x'),
(1 ,'x'),
(2 ,'b'),
(2 ,'b'),
(2 ,'y'),
(3 ,'c'),
(3 ,'d'),
(3 ,'z'),
(10,'c'),
(10,'c'),
(10,'z'),
(11,'x'),
(11,'y'),
(11,'z')) V(id, [value])
GROUP BY id
HAVING COUNT(CASE [value] WHEN 'a' THEN 1 WHEN 'b' THEN 1 END) > 0
OR (COUNT(CASE [value] WHEN 'c' THEN 1 END) > 0
AND COUNT(CASE [value] WHEN 'd' THEN 1 END) > 0);
answered Mar 7 at 16:53
LarnuLarnu
22.3k51833
answered Mar 7 at 16:53
LarnuLarnu
22.3k51833
answered Mar 7 at 16:53
LarnuLarnu
22.3k51833
answered Mar 7 at 16:53
LarnuLarnu
22.3k51833
22.3k51833
ahh I knew there was something simple like this that was I missing. Just what I was looking for, thanks!
– krock
Mar 7 at 17:02
add a comment |
ahh I knew there was something simple like this that was I missing. Just what I was looking for, thanks!
– krock
Mar 7 at 17:02
ahh I knew there was something simple like this that was I missing. Just what I was looking for, thanks!
– krock
Mar 7 at 17:02
ahh I knew there was something simple like this that was I missing. Just what I was looking for, thanks!
– krock
Mar 7 at 17:02
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%2f55048968%2fsql-server-is-there-a-better-way-for-me-to-select-based-on-values-in-varying-n%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
@PM77-1 care to elaborate? I'm not seeing how that would work
– krock
Mar 7 at 17:03
I misunderstood your task.
– PM 77-1
Mar 7 at 17:06
ah no worries, thanks anyway
– krock
Mar 7 at 17:16