SQL Server query doesn't return all stores

Question

I'm running around in circles and have been doing so in the last hours.

I'm doing a select in a table where have all stock of each store and I'm filtering by product id. What i need is: have a list of all stores even if I have no data for that store, but the select only returns 4 stores or less.

Here's an example:

That's the query:

select 
    store_id, product_id, start_date, quantity 
from 
    stock 
where 
    product_id = 407214

That's the result:

store_id | product_id |       start_date      | quantity |
    2    |   407214   |  2015-05-26 08:32:53  |    10    |
    3    |   407214   |  2015-03-16 12:10:00  |    25    |
    4    |   407214   |  2015-01-06 11:45:15  |    16    |
    7    |   407214   |  2015-05-14 00:00:00  |    8     |

And that's what I want:

store_id | product_id |       start_date      | quantity |
    1    |    NULL    |          NULL         |   NULL   |
    2    |   407214   |  2015-05-26 08:32:53  |    10    |
    3    |   407214   |  2015-03-16 12:10:00  |    25    |
    4    |   407214   |  2015-01-06 11:45:15  |    16    |
    5    |    NULL    |          NULL         |   NULL   |
    6    |    NULL    |          NULL         |   NULL   |
    7    |   407214   |  2015-05-14 00:00:00  |    8     |

I would really need a help, it's driving me crazy!!

even if i do select * from stock where product_id = 407214 or product_id is null , it return the same result — Alan Carvalho Chiganças, May 28 '15 at 12:46
If adding `product_id IS NULL` to your where clause doesn't fix it, then those values probably aren't `NULL`s — Duncan Howe, May 28 '15 at 12:48
Do you have a table that has all the store_id's you want in it? — Tab Alleman, May 28 '15 at 13:04
Yes Tab, but i did left, right, outer, full joins and no one returned me the result that i want — Alan Carvalho Chiganças, May 28 '15 at 13:23
I'm confused - why would you be using a join? You're selecting data from a single table according to your post. What are the results when you do `SELECT store_id, product_id, start_date, quantity FROM store`? If there's another table involved it should be included in the question. — Dan Field, May 28 '15 at 13:33

score 5 · Accepted Answer · answered May 28 '15 at 12:45

5

the solution depends on your db structure

if stock table only contains available products, you need left join of tables stores and stock

select 
  s.id as store_id, 
  st.product_id, 
  st.start_date, 
  st.quantity 
from 
stores s
left join stock st on s.Id = st.store_id and st.product_id = 407214

answered May 28 '15 at 12:45

ASh

34,632
9
60
82

Ash, i tried to do like you posted but the result still the same – Alan Carvalho Chiganças May 28 '15 at 13:20
@AlanCarvalhoChiganças, note that i removed `where` clause and moved filter by product_id into join condition. did you do the same? – ASh May 28 '15 at 13:29
Where are you getting that there's a stores table? – Dan Field May 28 '15 at 13:33
@DanField, `store_id` is likely a foreign key, so i made an assumption that db structure includes table `stores` – ASh May 28 '15 at 13:36
@ASh many thanks bro, you saved my life haha that's it, it worked – Alan Carvalho Chiganças May 28 '15 at 13:37

score 3 · Answer 2 · answered May 28 '15 at 12:38

3

Add OR product_id IS NULL to the WHERE clause

answered May 28 '15 at 12:38

TTeeple

2,913
1
13
22

Just a suggestion, but maybe explain why that fixes the issue. It's likely not obvious o the asker. – Dan Field May 28 '15 at 12:41
Nothing guys, returns the same results – Alan Carvalho Chiganças May 28 '15 at 12:43
Is that an actual NULL (meaning the column is nullable and is considered emtpy) or are you putting the word 'NULL' into the column? – TTeeple May 28 '15 at 12:45
even if i do select * from stock where product_id = 407214 or product_id is null , it return the same result – Alan Carvalho Chiganças May 28 '15 at 12:46
Then update your question. This query would produce the correct results as specified in your original question. – Dan Field May 28 '15 at 13:34
...I would never expect this to work, because you're not supposed to use null values in keys. The design of the table runs counter to data (probably) being stored this way. – Clockwork-Muse May 28 '15 at 14:04

Coding Enthusiast · Answer 3 · 2015-05-28T12:47:34.890

Is null will return the rows where there is no value. The or in the where clause is for condition. If the specific row means one of these conditions.

select store_id, product_id, start_date, quantity 
     from stock where 
          product_id = 407214 OR Product_id iS null

Or your null is a string ?

 select store_id, product_id, start_date, quantity 
     from stock where 
          product_id = 407214 OR Product_id like null

Or maybe you are inputting product_id as null you can try this:

select store_id, product_id, start_date, quantity 
    from stock where 
       product_id = 407214 OR Product_id = null

Or maybe if you want everything, remove the where clause but you order by product_id: (maybe this is what you mean)

select store_id, product_id, start_date, quantity from stock order by product_id

...I would never expect this to work, because you're not supposed to use null values in keys. The design of the table runs counter to data (probably) being stored this way. — Clockwork-Muse, May 28 '15 at 14:04

score 0 · Answer 4 · edited May 28 '15 at 13:04

0

select store_id, product_id, start_date, quantity from stock where product_id = 407214 or product_id = null

edited May 28 '15 at 13:04

Mariusz Jamro

30,615
24
120
162

answered May 28 '15 at 12:46

Dhru 'soni

1,024
7
23

...I would never expect this to work, because you're not supposed to use null values in keys. The design of the table runs counter to data (probably) being stored this way. – Clockwork-Muse May 28 '15 at 14:08

score 0 · Answer 5 · edited May 23 '17 at 12:05

0

Knowing how to generate fixed number of rows in a table you can:

--select 5 as store_id, 407214 as product_id, 3 as start_date, 3 as quantity into stock

DECLARE @maxStoreId int
SELECT 
    @maxStoreId = MAX(store_id) 
FROM 
    stock;

WITH r AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1 FROM r WHERE n+1<=@maxStoreId
)
SELECT 
    r.n as store_id, product_id, start_date, quantity  
FROM 
    r left outer join stock on  r.n = stock.store_id
WHERE 
    product_id = 407214 or product_id is null

--drop table stock

edited May 23 '17 at 12:05

Community

1
1

answered May 28 '15 at 12:59

sbiz

321
3
10

If you have a stores table, @ASh solution is better – sbiz May 28 '15 at 13:08
sbiz, i tried to do like you posted but the result still the same. the column n presents the same values that store_id – Alan Carvalho Chiganças May 28 '15 at 13:21
Try now. I modified the code so r.n acts as the store_id. Keep in mind that the store_ids are generated and may not exist in your data model – sbiz May 28 '15 at 13:25
1

You should put the `product_id` comparisons in the `ON` clause, which would allow you to get rid of `OR product_id IS NULL` – Clockwork-Muse May 28 '15 at 13:59

SQL Server query doesn't return all stores

5 Answers5