Sql Subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql Subquery

Author

Topic

alboi
Starting Member

12 Posts

Posted - 2005-08-08 : 15:38:41

I am new to sql and i was reading about subquery and i think its the right tool for what i want to achieve
i have two tables
Products table, OrdersLine table
Products Table
ProdSku
ProdName
QOH
Cost
********
OrdersLine Table
OrderNum
ProdSku
Qty

I want to get the product Sku, Name, QOH, Cost and the Sum(Qty) from OrdersLine
this is what i have tried

SELECT     dbo.Products.ProdSku AS Sku, dbo.OrdersLine.Qty AS Expr1
FROM         dbo.Products INNER JOIN
                      dbo.OrdersLine ON dbo.Products.ProdSku = dbo.OrdersLine.ProdSku
WHERE     (dbo.Products.ProdSku = '122345')
GROUP BY dbo.Products.ProdSku

please help!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-08 : 15:51:57

Well it's not a subquery


    SELECT P.ProdSku AS Sku, SUM(o.Qty) AS o.SUM_Qty
      FROM Products  P 
INNER JOIN OdersLine O 
	ON P.ProdSku = O.ProdSku
     WHERE P.ProdSku = '122345'
  GROUP BY P.ProdSku

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

alboi
Starting Member

12 Posts

Posted - 2005-08-08 : 15:55:39

You rock! thanks!!!

alboi
Starting Member

12 Posts

Posted - 2005-08-08 : 16:06:08

HMM

the code u gave me worked fine until i added another field from another table :(

SELECT     P.ProdSku AS Sku, SUM(o.Qty) AS SUM_Qty, v.VendorComp
FROM         dbo.Products P INNER JOIN
                      dbo.OrdersLine o ON P.ProdSku = o.ProdSku INNER JOIN
                      dbo.Vendor v ON P.VendorID = v.VendorID
WHERE     (P.ProdSku = '122345')
GROUP BY P.ProdSku

what am i doing wrong!!

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-08 : 16:50:43

quote:

Originally posted by alboi

HMM

the code u gave me worked fine until i added another field from another table :(

SELECT     P.ProdSku AS Sku, SUM(o.Qty) AS SUM_Qty, v.VendorComp
FROM         dbo.Products P INNER JOIN
                      dbo.OrdersLine o ON P.ProdSku = o.ProdSku INNER JOIN
                      dbo.Vendor v ON P.VendorID = v.VendorID
WHERE     (P.ProdSku = '122345')
GROUP BY P.ProdSku

what am i doing wrong!!

change your GROUP BY to

GROUP BY P.ProdSku, v.VendorComp

and see if that works

-ec

alboi
Starting Member

12 Posts

Posted - 2005-08-08 : 16:52:39

thanks :)

alboi
Starting Member

12 Posts

Posted - 2005-08-08 : 17:24:40

I noticed that once i did that it is return only those that have a valid SUM(qty) is there a way i can get even the products that have zero on order

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-08 : 18:08:00

Youll need to use a LEFT JOIN instead of the INNER.

With the LEFT JOIN, youll return everything from the first table (products) and all matching rows from the second (orders). NULLs with be present in the zero case you referred to (where zero orders exists for a product).

Look into ISNULL in BOL to handle these cases.

Nathan Skerl

alboi
Starting Member

12 Posts

Posted - 2005-08-08 : 20:50:04

i am sorry i am lost ...
SELECT P.ProdSku,p.prodName,p.QOH, p.Cost, SUM(o.Qty) AS OnOrd, v.VendorComp as VendorComp FROM Products P LEFT JOIN OrdersLine O ON P.ProdSku = O.ProdSku LEFT JOIN Vendor v ON P.VendorID = v.VendorID GROUP BY P.ProdSku, v.VendorComp,p.prodName,p.QOH, p.Cost
now since i have nulls do i have to put an if statment somewhere there??
thanks for the help!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-08 : 21:50:38

Yes, the ISNULL will do this for ya.

Example:

sum(isnull(qty,0))

Thats not true. Sorry... brainfart.

The SUM function already eliminates NULLs. So this shouldnt be an issue for you.

What is the problem you are experiencing?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources