SQL Server select parts, fill with 0 no resultHow 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?
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?
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 edited Mar 8 at 7:19
marc_s
582k13011231269
asked Mar 8 at 6:38
Juan GJuan G
407
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 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 edited Mar 8 at 7:19
marc_s
582k13011231269
asked Mar 8 at 6:38
Juan GJuan G
407
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 sql
sql-server 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
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
edited Mar 8 at 7:19
marc_s
582k13011231269
582k13011231269
asked Mar 8 at 6:38
Juan GJuan G
407
asked Mar 8 at 6:38
Juan GJuan G
407
asked Mar 8 at 6:38
Juan GJuan G
407
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 |
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
Can you provide sample script to generate data?
– Prashant Pimpale
Mar 8 at 6:41
Can you provide sample script to generate data?
– Prashant Pimpale
Mar 8 at 6:41
1
1
If you can provide your query, so we could apply fixes from there.
– jned29
Mar 8 at 6:43
If you can provide your query, so we could apply fixes from there.
– jned29
Mar 8 at 6:43
add a comment |
2 Answers
2
active
oldest
votes
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
answered Mar 8 at 6:48
Giorgos BetsosGiorgos Betsos
62.3k73265
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
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
);
);
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%2f55057951%2fsql-server-select-parts-fill-with-0-no-result%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
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
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
answered Mar 8 at 6:48
Giorgos BetsosGiorgos Betsos
62.3k73265
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
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
answered Mar 8 at 6:48
Giorgos BetsosGiorgos Betsos
62.3k73265
62.3k73265
add a comment |
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
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
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
answered Mar 8 at 12:11
NIKHIL THAKURNIKHIL THAKUR
211
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
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
answered Mar 8 at 12:11
NIKHIL THAKURNIKHIL THAKUR
211
211
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.
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%2f55057951%2fsql-server-select-parts-fill-with-0-no-result%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
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