CODEWITHSUNDEEP
sqlsql-server
Alan Carvalho Chiganças
Alan Carvalho Chiganças

Reputation: 19

SQL Server query doesn't return all stores

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!!

Upvotes: 0

Views: 100

Answers (5)

sbiz
sbiz

Reputation: 321

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

Upvotes: 0

Dhru &#39;soni
Dhru &#39;soni

Reputation: 1058

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

Upvotes: 0

Coding Enthusiast
Coding Enthusiast

Reputation: 3933

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

Upvotes: 0

ASh
ASh

Reputation: 35722

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

Upvotes: 5

TTeeple
TTeeple

Reputation: 2989

Add OR product_id IS NULL to the WHERE clause

Upvotes: 3

Related Questions