Ggtcf

Proving a function is onto where f(x)=|x|.

Indicating multiple different modes of speech (fantasy language or telepathy)

Journal losing indexing services

Greco-Roman egalitarianism

Is there a word to describe the feeling of being transfixed out of horror?

We have a love-hate relationship

Flux received by a negative charge

How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?

What major Native American tribes were around Santa Fe during the late 1850s?

Global amount of publications over time

Is it improper etiquette to ask your opponent what his/her rating is before the game?

Why did the HMS Bounty go back to a time when whales are already rare?

How to align and center standalone amsmath equations?

Could solar power be utilized and substitute coal in the 19th Century

Is a model fitted to data or is data fitted to a model?

What linear sensor for a keyboard?

What is the difference between "Do you interest" and "...interested in" something?

Customize circled numbers

Wrapping Cryptocurrencies for interoperability sake

Is camera lens focus an exact point or a range?

How to color a curve

Could the E-bike drivetrain wear down till needing replacement after 400 km?

Drawing a topological "handle" with Tikz

If a character with the Alert feat rolls a crit fail on their Perception check, are they surprised?

SQL Server select parts, fill with 0 no result

How can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?Insert results of a stored procedure into a temporary tableRetrieving the last record in each group - MySQLHow do I UPDATE from a SELECT in SQL Server?

0

I have the following tables

• Location (Id, locationName)


• Inventory (productid, qty, locationid)

With the following data, I need to query to show all locations per productid, even when not in inventory table. example of records below.

Table Location

Id Location Name
--------------------
1 Plant
2 Warehouse
3 Container

Table Inventory:

Productid Qty Locationid
-----------------------------
 45 30 1
 45 56 2
 3 15 1 
 3 50 3 
 15 25 3

Desired result for my query:

Productid Qty LocationName
---------------------------------
 45 30 Plant
 45 56 Warehouse
 45 0 Container
 3 15 Plant
 3 0 Warehouse
 3 50 Container
 15 0 Plant
 15 0 Warehouse
 15 25 Container

So far I have tried many different ways but no luck, so any help will be appreciated.

sql sql-server

share|improve this question

edited Mar 8 at 7:19

marc_s

582k13011231269

asked Mar 8 at 6:38

Juan GJuan G

407

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you provide sample script to generate data?
  
  – Prashant Pimpale
  Mar 8 at 6:41
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If you can provide your query, so we could apply fixes from there.
  
  – jned29
  Mar 8 at 6:43
  

  
  
  
  

add a comment | 

0

I have the following tables

• Location (Id, locationName)


• Inventory (productid, qty, locationid)

With the following data, I need to query to show all locations per productid, even when not in inventory table. example of records below.

Table Location

Id Location Name
--------------------
1 Plant
2 Warehouse
3 Container

Table Inventory:

Productid Qty Locationid
-----------------------------
 45 30 1
 45 56 2
 3 15 1 
 3 50 3 
 15 25 3

Desired result for my query:

Productid Qty LocationName
---------------------------------
 45 30 Plant
 45 56 Warehouse
 45 0 Container
 3 15 Plant
 3 0 Warehouse
 3 50 Container
 15 0 Plant
 15 0 Warehouse
 15 25 Container

So far I have tried many different ways but no luck, so any help will be appreciated.

sql sql-server

share|improve this question

edited Mar 8 at 7:19

marc_s

582k13011231269

asked Mar 8 at 6:38

Juan GJuan G

407

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you provide sample script to generate data?
  
  – Prashant Pimpale
  Mar 8 at 6:41
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If you can provide your query, so we could apply fixes from there.
  
  – jned29
  Mar 8 at 6:43
  

  
  
  
  

add a comment | 

I have the following tables

• Location (Id, locationName)


• Inventory (productid, qty, locationid)

With the following data, I need to query to show all locations per productid, even when not in inventory table. example of records below.

Table Location

Id Location Name
--------------------
1 Plant
2 Warehouse
3 Container

Table Inventory:

Productid Qty Locationid
-----------------------------
 45 30 1
 45 56 2
 3 15 1 
 3 50 3 
 15 25 3

Desired result for my query:

Productid Qty LocationName
---------------------------------
 45 30 Plant
 45 56 Warehouse
 45 0 Container
 3 15 Plant
 3 0 Warehouse
 3 50 Container
 15 0 Plant
 15 0 Warehouse
 15 25 Container

So far I have tried many different ways but no luck, so any help will be appreciated.

sql sql-server

share|improve this question

edited Mar 8 at 7:19

marc_s

582k13011231269

asked Mar 8 at 6:38

Juan GJuan G

407

Id Location Name
--------------------
1 Plant
2 Warehouse
3 Container

Productid Qty Locationid
-----------------------------
 45 30 1
 45 56 2
 3 15 1 
 3 50 3 
 15 25 3

Productid Qty LocationName
---------------------------------
 45 30 Plant
 45 56 Warehouse
 45 0 Container
 3 15 Plant
 3 0 Warehouse
 3 50 Container
 15 0 Plant
 15 0 Warehouse
 15 25 Container

sql sql-server

sql sql-server

share|improve this question

edited Mar 8 at 7:19

marc_s

582k13011231269

asked Mar 8 at 6:38

Juan GJuan G

407

share|improve this question

edited Mar 8 at 7:19

marc_s

582k13011231269

asked Mar 8 at 6:38

Juan GJuan G

407

edited Mar 8 at 7:19

marc_s

582k13011231269

edited Mar 8 at 7:19

marc_s

582k13011231269

asked Mar 8 at 6:38

Juan GJuan G

407

asked Mar 8 at 6:38

Juan GJuan G

407

2 Answers
2

active

oldest

votes

7

You can use the following query:

SELECT p.ProductId, 
 COALESCE(qty,0) AS qty,
 [Location Name]
FROM LOCATION l
CROSS JOIN (SELECT DISTINCT ProductId FROM Inventory) AS p
LEFT JOIN Inventory i ON l.Id = i.locationid AND p.Productid = i.Productid
ORDER BY Productid, [Location Name] 

The query uses CROSS JOIN to get all possible combinations between locations and products.

Demo here

share|improve this answer

answered Mar 8 at 6:48

Giorgos BetsosGiorgos Betsos

62.3k73265

add a comment | 

0

Select y., isNull(z.Quantity,0) as Quantity
From
(
Select Location., x.ProductId

From Location,(Select Distinct ProductId From Inventory) as x
) as y
Left Outer Join Inventory z ON y.Id = z.LocationId
and y.ProductId = z.ProductId

share|improve this answer

answered Mar 8 at 12:11

NIKHIL THAKURNIKHIL THAKUR

211

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55057951%2fsql-server-select-parts-fill-with-0-no-result%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

7

You can use the following query:

SELECT p.ProductId, 
 COALESCE(qty,0) AS qty,
 [Location Name]
FROM LOCATION l
CROSS JOIN (SELECT DISTINCT ProductId FROM Inventory) AS p
LEFT JOIN Inventory i ON l.Id = i.locationid AND p.Productid = i.Productid
ORDER BY Productid, [Location Name] 

The query uses CROSS JOIN to get all possible combinations between locations and products.

Demo here

share|improve this answer

answered Mar 8 at 6:48

Giorgos BetsosGiorgos Betsos

62.3k73265

add a comment | 

7

You can use the following query:

SELECT p.ProductId, 
 COALESCE(qty,0) AS qty,
 [Location Name]
FROM LOCATION l
CROSS JOIN (SELECT DISTINCT ProductId FROM Inventory) AS p
LEFT JOIN Inventory i ON l.Id = i.locationid AND p.Productid = i.Productid
ORDER BY Productid, [Location Name] 

The query uses CROSS JOIN to get all possible combinations between locations and products.

Demo here

share|improve this answer

answered Mar 8 at 6:48

Giorgos BetsosGiorgos Betsos

62.3k73265

add a comment | 

You can use the following query:

SELECT p.ProductId, 
 COALESCE(qty,0) AS qty,
 [Location Name]
FROM LOCATION l
CROSS JOIN (SELECT DISTINCT ProductId FROM Inventory) AS p
LEFT JOIN Inventory i ON l.Id = i.locationid AND p.Productid = i.Productid
ORDER BY Productid, [Location Name] 

The query uses CROSS JOIN to get all possible combinations between locations and products.

Demo here

share|improve this answer

answered Mar 8 at 6:48

Giorgos BetsosGiorgos Betsos

62.3k73265

SELECT p.ProductId, 
 COALESCE(qty,0) AS qty,
 [Location Name]
FROM LOCATION l
CROSS JOIN (SELECT DISTINCT ProductId FROM Inventory) AS p
LEFT JOIN Inventory i ON l.Id = i.locationid AND p.Productid = i.Productid
ORDER BY Productid, [Location Name] 

share|improve this answer

answered Mar 8 at 6:48

Giorgos BetsosGiorgos Betsos

62.3k73265

answered Mar 8 at 6:48

Giorgos BetsosGiorgos Betsos

62.3k73265

answered Mar 8 at 6:48

Giorgos BetsosGiorgos Betsos

62.3k73265

0

Select y., isNull(z.Quantity,0) as Quantity
From
(
Select Location., x.ProductId

From Location,(Select Distinct ProductId From Inventory) as x
) as y
Left Outer Join Inventory z ON y.Id = z.LocationId
and y.ProductId = z.ProductId

share|improve this answer

answered Mar 8 at 12:11

NIKHIL THAKURNIKHIL THAKUR

211

add a comment | 

0

Select y., isNull(z.Quantity,0) as Quantity
From
(
Select Location., x.ProductId

From Location,(Select Distinct ProductId From Inventory) as x
) as y
Left Outer Join Inventory z ON y.Id = z.LocationId
and y.ProductId = z.ProductId

share|improve this answer

answered Mar 8 at 12:11

NIKHIL THAKURNIKHIL THAKUR

211

add a comment | 

Select y., isNull(z.Quantity,0) as Quantity
From
(
Select Location., x.ProductId

From Location,(Select Distinct ProductId From Inventory) as x
) as y
Left Outer Join Inventory z ON y.Id = z.LocationId
and y.ProductId = z.ProductId

share|improve this answer

answered Mar 8 at 12:11

NIKHIL THAKURNIKHIL THAKUR

211

share|improve this answer

answered Mar 8 at 12:11

NIKHIL THAKURNIKHIL THAKUR

211

answered Mar 8 at 12:11

NIKHIL THAKURNIKHIL THAKUR

211

answered Mar 8 at 12:11

NIKHIL THAKURNIKHIL THAKUR

211