Returning rows even when query value is not found
up vote
1
down vote
favorite
I have a query like below in SQL. I would like to return Item with quantity: 0 even when it does not return any data (i.e. 590209, 590212 does not exist in my records). I have probably 10000 records I need to return. Any idea how to do it?
SELECT [Item] ,[Qty]
FROM Table
where Item in (590209,590210,590211,590212,590213)
Results should be like
Item Qty
590209 0
590210 3
590211 1
590212 0
590213 1
sql
edited Nov 9 at 10:14
Mukyuu
3191216
asked Nov 9 at 8:15
user10627737
82
add a comment |
up vote
1
down vote
favorite
I have a query like below in SQL. I would like to return Item with quantity: 0 even when it does not return any data (i.e. 590209, 590212 does not exist in my records). I have probably 10000 records I need to return. Any idea how to do it?
SELECT [Item] ,[Qty]
FROM Table
where Item in (590209,590210,590211,590212,590213)
Results should be like
Item Qty
590209 0
590210 3
590211 1
590212 0
590213 1
sql
edited Nov 9 at 10:14
Mukyuu
3191216
asked Nov 9 at 8:15
user10627737
82
2
which db you are using??
– scaisEdge
Nov 9 at 8:17
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a query like below in SQL. I would like to return Item with quantity: 0 even when it does not return any data (i.e. 590209, 590212 does not exist in my records). I have probably 10000 records I need to return. Any idea how to do it?
SELECT [Item] ,[Qty]
FROM Table
where Item in (590209,590210,590211,590212,590213)
Results should be like
Item Qty
590209 0
590210 3
590211 1
590212 0
590213 1
sql
edited Nov 9 at 10:14
Mukyuu
3191216
asked Nov 9 at 8:15
user10627737
82
I have a query like below in SQL. I would like to return Item with quantity: 0 even when it does not return any data (i.e. 590209, 590212 does not exist in my records). I have probably 10000 records I need to return. Any idea how to do it?
SELECT [Item] ,[Qty]
FROM Table
where Item in (590209,590210,590211,590212,590213)
Results should be like
Item Qty
590209 0
590210 3
590211 1
590212 0
590213 1
sql
sql
edited Nov 9 at 10:14
Mukyuu
3191216
asked Nov 9 at 8:15
user10627737
82
edited Nov 9 at 10:14
Mukyuu
3191216
asked Nov 9 at 8:15
user10627737
82
edited Nov 9 at 10:14
Mukyuu
3191216
edited Nov 9 at 10:14
Mukyuu
3191216
edited Nov 9 at 10:14
Mukyuu
3191216
3191216
asked Nov 9 at 8:15
user10627737
82
asked Nov 9 at 8:15
user10627737
82
asked Nov 9 at 8:15
user10627737
82
82
2
which db you are using??
– scaisEdge
Nov 9 at 8:17
add a comment |
2
which db you are using??
– scaisEdge
Nov 9 at 8:17
2
2
which db you are using??
– scaisEdge
Nov 9 at 8:17
which db you are using??
– scaisEdge
Nov 9 at 8:17
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
accepted
try like below
select t.Item,t1.Qty from
(
select 590209 as Item
union all
select 590210
union all
select 590211
union all
select 590212
union all
select 590213
) t left join your_table t1
on t.Item=t1.Item
answered Nov 9 at 8:19
Zaynul Abadin Tuhin
10.6k2731
add a comment |
up vote
0
down vote
You can try like below to return "Item" with quantity 0
SELECT [Item] ,[Qty]
FROM Table
where Qty = 0
answered Nov 9 at 8:16
Sanal Sunny
5787
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
try like below
select t.Item,t1.Qty from
(
select 590209 as Item
union all
select 590210
union all
select 590211
union all
select 590212
union all
select 590213
) t left join your_table t1
on t.Item=t1.Item
answered Nov 9 at 8:19
Zaynul Abadin Tuhin
10.6k2731
add a comment |
up vote
0
down vote
accepted
try like below
select t.Item,t1.Qty from
(
select 590209 as Item
union all
select 590210
union all
select 590211
union all
select 590212
union all
select 590213
) t left join your_table t1
on t.Item=t1.Item
answered Nov 9 at 8:19
Zaynul Abadin Tuhin
10.6k2731
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
try like below
select t.Item,t1.Qty from
(
select 590209 as Item
union all
select 590210
union all
select 590211
union all
select 590212
union all
select 590213
) t left join your_table t1
on t.Item=t1.Item
answered Nov 9 at 8:19
Zaynul Abadin Tuhin
10.6k2731
try like below
select t.Item,t1.Qty from
(
select 590209 as Item
union all
select 590210
union all
select 590211
union all
select 590212
union all
select 590213
) t left join your_table t1
on t.Item=t1.Item
answered Nov 9 at 8:19
Zaynul Abadin Tuhin
10.6k2731
answered Nov 9 at 8:19
Zaynul Abadin Tuhin
10.6k2731
answered Nov 9 at 8:19
Zaynul Abadin Tuhin
10.6k2731
answered Nov 9 at 8:19
Zaynul Abadin Tuhin
10.6k2731
10.6k2731
add a comment |
add a comment |
up vote
0
down vote
You can try like below to return "Item" with quantity 0
SELECT [Item] ,[Qty]
FROM Table
where Qty = 0
answered Nov 9 at 8:16
Sanal Sunny
5787
add a comment |
up vote
0
down vote
You can try like below to return "Item" with quantity 0
SELECT [Item] ,[Qty]
FROM Table
where Qty = 0
answered Nov 9 at 8:16
Sanal Sunny
5787
add a comment |
up vote
0
down vote
up vote
0
down vote
You can try like below to return "Item" with quantity 0
SELECT [Item] ,[Qty]
FROM Table
where Qty = 0
answered Nov 9 at 8:16
Sanal Sunny
5787
You can try like below to return "Item" with quantity 0
SELECT [Item] ,[Qty]
FROM Table
where Qty = 0
answered Nov 9 at 8:16
Sanal Sunny
5787
answered Nov 9 at 8:16
Sanal Sunny
5787
answered Nov 9 at 8:16
Sanal Sunny
5787
answered Nov 9 at 8:16
Sanal Sunny
5787
5787
add a comment |
add a comment |
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%2f53222004%2freturning-rows-even-when-query-value-is-not-found%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
2
which db you are using??
– scaisEdge
Nov 9 at 8:17