Why is this SQL case statement behaving like an OR statement?
up vote
1
down vote
favorite
Consider the following query:
declare @RentalId int = 1
SELECT
r.RentalId
,r.[Name]
,rt.TypeId
FROM dbo.Rental r
LEFT JOIN dbo.RentalRateType rt ON (
r.RentalId = rt.RentalId
AND rt.TypeId = (
case when rt.TypeId = 6 and coalesce(rt.[Max], rt.[Min]) is not null then 6
when rt.TypeId = 1 and coalesce(rt.[Max], rt.[Min] is not null then 1
else -1 end
))
WHERE r.RentalId = @RentalId
I'm attempting to return a single record/row. The particular rental in question has 2 records in the dbo.RentalRateType table, and when I run the above query, I get 2 results, but I want it to short circuit on the first match in the case where.
Basically, the end user can fill in multiple rate types, more than what you see in this example, and each of those types has a priority. 6 is the highest priority in the example.
So I'm getting this result:
RentalId | Name | TypeId
----------------------------
1 Super Car 6
1 Super Car 1
But if the type (6) exists, I would expect only the first row above returned.
I must be missing something silly. This works as expected:
case when 1=2 then 6
when 1=1 then 1
else -1 end
While I'm here, I'm open to a more efficient manner of handling this if exists.
sql-server tsql sql-server-2016 asked Nov 19 at 23:51
user1447679
1,29341745
|
show 1 more comment
up vote
1
down vote
favorite
Consider the following query:
declare @RentalId int = 1
SELECT
r.RentalId
,r.[Name]
,rt.TypeId
FROM dbo.Rental r
LEFT JOIN dbo.RentalRateType rt ON (
r.RentalId = rt.RentalId
AND rt.TypeId = (
case when rt.TypeId = 6 and coalesce(rt.[Max], rt.[Min]) is not null then 6
when rt.TypeId = 1 and coalesce(rt.[Max], rt.[Min] is not null then 1
else -1 end
))
WHERE r.RentalId = @RentalId
I'm attempting to return a single record/row. The particular rental in question has 2 records in the dbo.RentalRateType table, and when I run the above query, I get 2 results, but I want it to short circuit on the first match in the case where.
Basically, the end user can fill in multiple rate types, more than what you see in this example, and each of those types has a priority. 6 is the highest priority in the example.
So I'm getting this result:
RentalId | Name | TypeId
----------------------------
1 Super Car 6
1 Super Car 1
But if the type (6) exists, I would expect only the first row above returned.
I must be missing something silly. This works as expected:
case when 1=2 then 6
when 1=1 then 1
else -1 end
While I'm here, I'm open to a more efficient manner of handling this if exists.
sql-server tsql sql-server-2016 asked Nov 19 at 23:51
user1447679
1,29341745
I'm not sure on your specific requirements but the reason you're getting two results is because both fulfill the case expression's conditions... Maybe you're just looking for aTOP 1with an order by based on whatever you're looking for?
– ZLK
Nov 20 at 0:13
Why have you written aninner joinas aleft outer joinwith a reference to the right-hand table in thewhereclause (... and rt.TypeId = ...) that doesn't allow nulls?
– HABO
Nov 20 at 0:13
@HABO I need to account for situations where no rate has been entered and still return a record (reason for left join) -edit- yes I see what you mean with the WHERE clause. That's because I was moving it around to test (Was within the ON (... ).
– user1447679
Nov 20 at 0:15
@HABO I'll fix my example
– user1447679
Nov 20 at 0:17
@ZLK Right. My goal is to return a single row. If the user has a rate type of 6, use that one, otherwise use 1. Now in my real application there are more like 5 different rate types, each having a priority.
– user1447679
Nov 20 at 0:21
|
show 1 more comment
up vote
1
down vote
favorite
up vote
1
down vote
favorite
Consider the following query:
declare @RentalId int = 1
SELECT
r.RentalId
,r.[Name]
,rt.TypeId
FROM dbo.Rental r
LEFT JOIN dbo.RentalRateType rt ON (
r.RentalId = rt.RentalId
AND rt.TypeId = (
case when rt.TypeId = 6 and coalesce(rt.[Max], rt.[Min]) is not null then 6
when rt.TypeId = 1 and coalesce(rt.[Max], rt.[Min] is not null then 1
else -1 end
))
WHERE r.RentalId = @RentalId
I'm attempting to return a single record/row. The particular rental in question has 2 records in the dbo.RentalRateType table, and when I run the above query, I get 2 results, but I want it to short circuit on the first match in the case where.
Basically, the end user can fill in multiple rate types, more than what you see in this example, and each of those types has a priority. 6 is the highest priority in the example.
So I'm getting this result:
RentalId | Name | TypeId
----------------------------
1 Super Car 6
1 Super Car 1
But if the type (6) exists, I would expect only the first row above returned.
I must be missing something silly. This works as expected:
case when 1=2 then 6
when 1=1 then 1
else -1 end
While I'm here, I'm open to a more efficient manner of handling this if exists.
sql-server tsql sql-server-2016 asked Nov 19 at 23:51
user1447679
1,29341745
Consider the following query:
declare @RentalId int = 1
SELECT
r.RentalId
,r.[Name]
,rt.TypeId
FROM dbo.Rental r
LEFT JOIN dbo.RentalRateType rt ON (
r.RentalId = rt.RentalId
AND rt.TypeId = (
case when rt.TypeId = 6 and coalesce(rt.[Max], rt.[Min]) is not null then 6
when rt.TypeId = 1 and coalesce(rt.[Max], rt.[Min] is not null then 1
else -1 end
))
WHERE r.RentalId = @RentalId
I'm attempting to return a single record/row. The particular rental in question has 2 records in the dbo.RentalRateType table, and when I run the above query, I get 2 results, but I want it to short circuit on the first match in the case where.
Basically, the end user can fill in multiple rate types, more than what you see in this example, and each of those types has a priority. 6 is the highest priority in the example.
So I'm getting this result:
RentalId | Name | TypeId
----------------------------
1 Super Car 6
1 Super Car 1
But if the type (6) exists, I would expect only the first row above returned.
I must be missing something silly. This works as expected:
case when 1=2 then 6
when 1=1 then 1
else -1 end
While I'm here, I'm open to a more efficient manner of handling this if exists.
sql-server tsql sql-server-2016 sql-server tsql sql-server-2016 asked Nov 19 at 23:51
user1447679
1,29341745
asked Nov 19 at 23:51
user1447679
1,29341745
edited Nov 20 at 0:19
asked Nov 19 at 23:51
user1447679
1,29341745
asked Nov 19 at 23:51
user1447679
1,29341745
asked Nov 19 at 23:51
user1447679
1,29341745
1,29341745
I'm not sure on your specific requirements but the reason you're getting two results is because both fulfill the case expression's conditions... Maybe you're just looking for aTOP 1with an order by based on whatever you're looking for?
– ZLK
Nov 20 at 0:13
Why have you written aninner joinas aleft outer joinwith a reference to the right-hand table in thewhereclause (... and rt.TypeId = ...) that doesn't allow nulls?
– HABO
Nov 20 at 0:13
@HABO I need to account for situations where no rate has been entered and still return a record (reason for left join) -edit- yes I see what you mean with the WHERE clause. That's because I was moving it around to test (Was within the ON (... ).
– user1447679
Nov 20 at 0:15
@HABO I'll fix my example
– user1447679
Nov 20 at 0:17
@ZLK Right. My goal is to return a single row. If the user has a rate type of 6, use that one, otherwise use 1. Now in my real application there are more like 5 different rate types, each having a priority.
– user1447679
Nov 20 at 0:21
|
show 1 more comment
I'm not sure on your specific requirements but the reason you're getting two results is because both fulfill the case expression's conditions... Maybe you're just looking for aTOP 1with an order by based on whatever you're looking for?
– ZLK
Nov 20 at 0:13
Why have you written aninner joinas aleft outer joinwith a reference to the right-hand table in thewhereclause (... and rt.TypeId = ...) that doesn't allow nulls?
– HABO
Nov 20 at 0:13
@HABO I need to account for situations where no rate has been entered and still return a record (reason for left join) -edit- yes I see what you mean with the WHERE clause. That's because I was moving it around to test (Was within the ON (... ).
– user1447679
Nov 20 at 0:15
@HABO I'll fix my example
– user1447679
Nov 20 at 0:17
@ZLK Right. My goal is to return a single row. If the user has a rate type of 6, use that one, otherwise use 1. Now in my real application there are more like 5 different rate types, each having a priority.
– user1447679
Nov 20 at 0:21
I'm not sure on your specific requirements but the reason you're getting two results is because both fulfill the case expression's conditions... Maybe you're just looking for a
TOP 1 with an order by based on whatever you're looking for?– ZLK
Nov 20 at 0:13
I'm not sure on your specific requirements but the reason you're getting two results is because both fulfill the case expression's conditions... Maybe you're just looking for a
TOP 1 with an order by based on whatever you're looking for?– ZLK
Nov 20 at 0:13
Why have you written an
inner join as a left outer join with a reference to the right-hand table in the where clause (... and rt.TypeId = ...) that doesn't allow nulls?– HABO
Nov 20 at 0:13
Why have you written an
inner join as a left outer join with a reference to the right-hand table in the where clause (... and rt.TypeId = ...) that doesn't allow nulls?– HABO
Nov 20 at 0:13
@HABO I need to account for situations where no rate has been entered and still return a record (reason for left join) -edit- yes I see what you mean with the WHERE clause. That's because I was moving it around to test (Was within the ON (... ).
– user1447679
Nov 20 at 0:15
@HABO I need to account for situations where no rate has been entered and still return a record (reason for left join) -edit- yes I see what you mean with the WHERE clause. That's because I was moving it around to test (Was within the ON (... ).
– user1447679
Nov 20 at 0:15
@HABO I'll fix my example
– user1447679
Nov 20 at 0:17
@HABO I'll fix my example
– user1447679
Nov 20 at 0:17
@ZLK Right. My goal is to return a single row. If the user has a rate type of 6, use that one, otherwise use 1. Now in my real application there are more like 5 different rate types, each having a priority.
– user1447679
Nov 20 at 0:21
@ZLK Right. My goal is to return a single row. If the user has a rate type of 6, use that one, otherwise use 1. Now in my real application there are more like 5 different rate types, each having a priority.
– user1447679
Nov 20 at 0:21
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
Use an apply instead, these are an efficient way to get "top n" queries:
SELECT
r.RentalId
, r.[Name]
, oa.TypeId
FROM dbo.Rental r
OUTER APPLY (
SELECT TOP (1)
rt.TypeId
FROM dbo.RentalRateType rt
WHERE r.RentalId = rt.RentalId
ORDER BY
rt.TypeId DESC
) oa
WHERE r.RentalId = @RentalId
answered Nov 20 at 0:46
Used_By_Already
22.2k21838
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',
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%2f53384304%2fwhy-is-this-sql-case-statement-behaving-like-an-or-statement%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
up vote
0
down vote
Use an apply instead, these are an efficient way to get "top n" queries:
SELECT
r.RentalId
, r.[Name]
, oa.TypeId
FROM dbo.Rental r
OUTER APPLY (
SELECT TOP (1)
rt.TypeId
FROM dbo.RentalRateType rt
WHERE r.RentalId = rt.RentalId
ORDER BY
rt.TypeId DESC
) oa
WHERE r.RentalId = @RentalId
answered Nov 20 at 0:46
Used_By_Already
22.2k21838
add a comment |
up vote
0
down vote
Use an apply instead, these are an efficient way to get "top n" queries:
SELECT
r.RentalId
, r.[Name]
, oa.TypeId
FROM dbo.Rental r
OUTER APPLY (
SELECT TOP (1)
rt.TypeId
FROM dbo.RentalRateType rt
WHERE r.RentalId = rt.RentalId
ORDER BY
rt.TypeId DESC
) oa
WHERE r.RentalId = @RentalId
answered Nov 20 at 0:46
Used_By_Already
22.2k21838
add a comment |
up vote
0
down vote
up vote
0
down vote
Use an apply instead, these are an efficient way to get "top n" queries:
SELECT
r.RentalId
, r.[Name]
, oa.TypeId
FROM dbo.Rental r
OUTER APPLY (
SELECT TOP (1)
rt.TypeId
FROM dbo.RentalRateType rt
WHERE r.RentalId = rt.RentalId
ORDER BY
rt.TypeId DESC
) oa
WHERE r.RentalId = @RentalId
answered Nov 20 at 0:46
Used_By_Already
22.2k21838
Use an apply instead, these are an efficient way to get "top n" queries:
SELECT
r.RentalId
, r.[Name]
, oa.TypeId
FROM dbo.Rental r
OUTER APPLY (
SELECT TOP (1)
rt.TypeId
FROM dbo.RentalRateType rt
WHERE r.RentalId = rt.RentalId
ORDER BY
rt.TypeId DESC
) oa
WHERE r.RentalId = @RentalId
answered Nov 20 at 0:46
Used_By_Already
22.2k21838
answered Nov 20 at 0:46
Used_By_Already
22.2k21838
answered Nov 20 at 0:46
Used_By_Already
22.2k21838
answered Nov 20 at 0:46
Used_By_Already
22.2k21838
22.2k21838
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%2f53384304%2fwhy-is-this-sql-case-statement-behaving-like-an-or-statement%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
I'm not sure on your specific requirements but the reason you're getting two results is because both fulfill the case expression's conditions... Maybe you're just looking for a
TOP 1with an order by based on whatever you're looking for?– ZLK
Nov 20 at 0:13
Why have you written an
inner joinas aleft outer joinwith a reference to the right-hand table in thewhereclause (... and rt.TypeId = ...) that doesn't allow nulls?– HABO
Nov 20 at 0:13
@HABO I need to account for situations where no rate has been entered and still return a record (reason for left join) -edit- yes I see what you mean with the WHERE clause. That's because I was moving it around to test (Was within the ON (... ).
– user1447679
Nov 20 at 0:15
@HABO I'll fix my example
– user1447679
Nov 20 at 0:17
@ZLK Right. My goal is to return a single row. If the user has a rate type of 6, use that one, otherwise use 1. Now in my real application there are more like 5 different rate types, each having a priority.
– user1447679
Nov 20 at 0:21