CODEWITHSUNDEEP
sqlsql-servert-sql
michox2
michox2

Reputation: 123

using subquery in order to join columns from two tables

i started learning SQL and there is something i dont understand

i want to take the columns product_id and product_name from Production.products and join it with the quantity column from the Production.stocks table but instead of using join i want to use a subquery.

this is the code i wrote so far: and i don't understand why it isn't working :(

SELECT P.product_id, P.product_name,(
SELECT S.quantity 
FROM Production.stocks AS S
WHERE S.product_id = P.product_id)
FROM Production.products as P;

the database i am working with

Upvotes: 0

Views: 3263

Answers (4)

kutsoff
kutsoff

Reputation: 345

did you mean select all data from products table and show quantity column for each product?

SELECT P.product_id, P.product_name, isnull(s.quantity, 0) as Quantity 
FROM Production.products as P
left join Production.stocks AS S
    on p.product_id = S.product_id

if you have one to many relation with Products and Stocks you should use subquery like this

SELECT P.product_id, P.product_name, isnull(s.quantity, 0) as Quantity 
FROM Production.products as P
left join ( 
   select product_id, sum(quantity) as Quantity
   from Production.stocks
   group by product_id)
as S on p.product_id = S.product_id

it will be produced aggregated sum value for quantity field

Upvotes: 1

LinkOps
LinkOps

Reputation: 341

I don't see a need at all for a subquery.

If the products are unique entities then surely a join onto the stocks table and doing a sum on the quantity would be more beneficial in terms of query performance

SELECT 
    Production.Products.Product_id, 
    Production.Products.product_name,
    SUM(Production.Stocks.quantity) AS Quantity
FROM 
    Production.Products 
LEFT JOIN
    Production.Stocks 
    ON 
        Production.Stocks.product_id = Production.Products.product_id
GROUP BY
    Production.Products.product_id, 
    Production.Products.product_name

If you need it to quote stock quantities by store then you need to add an addition join onto stores and add the store to the select and group by clause like so

SELECT 
    Production.Products.Product_id, 
    Production.Products.product_name,
    Sales.Stores.store_name,
    SUM(Production.Stocks.quantity) AS Quantity
FROM 
    Production.Products 
LEFT JOIN
    Production.Stocks 
    ON 
        Production.Stocks.product_id = Production.Products.product_id
LEFT JOIN
    Sales.Stores
    ON
        Production.Stocks.store_id = Sales.Stores.store_id
GROUP BY
    Production.Products.product_id, 
    Production.Products.product_name,
    Sales.Stores.store_name

Hope that helps

Upvotes: 1

hsyn.ozkara
hsyn.ozkara

Reputation: 127

You can use SUM keyword for prevent error and give you total quatity.

SELECT P.product_id, P.product_name,(
SELECT SUM(S.quantity) 
FROM Production.stocks AS S
WHERE S.product_id = P.product_id)
FROM Production.products as P;

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

First, let's clear up the fact that it is not recommended to use a subquery at all. Do it only for your own research reasons; if you have performance or code clarity in mind, go with the simple join.

When you make a subquery on the SELECT clause by enclosing it in parenthesis, you are forcing the result to be one single value. If not, you get the error you receive.

Usually, subqueries are used in the FROM clause, where they should be given a name and then represent a table. Like this:

SELECT P.product_id, P.product_name,S.quantity
FROM Production.products as P
inner join 
(
    SELECT quantity 
    FROM Production.stocks 
) as S on S.product_id = P.product_id

You can see from the simplicity of the subquery of how little use it is.

Upvotes: 1

Related Questions