Plz Urgent - 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

Plz Urgent

Author

Topic

vinoop
Starting Member

4 Posts

Posted - 2008-11-19 : 07:12:53

Hi i m new to sql server.

Let me explain my doubt.
Three tables - Product , Order , OrderDetail .

My ultimate result is take all the rows from Products with a column value in OrderDetail through a common column(ProductId) between Products and OrderDetail .

i could not join Products and OrderDetail directly, Since Many Cust can have same products in common.

Order and OrderDetail tables can be joined through orderNo.

i have to get the products.ProductId with one more column(null or value) depend upon it present in OrderDetail.

i have to filter the values by CustId column in Order.

Please explain .very Urgent

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:19:06

SELECT *
FROM Order AS o
INNER JOIN OrderDetail AS od ON do.OrderNo = o.OrderNo
INNER JOIN Products AS p ON p.ProductID = od.ProductID

E 12°55'05.63"
N 56°04'39.26"

vinoop
Starting Member

4 Posts

Posted - 2008-11-19 : 07:25:06

quote:
Originally posted by Peso

SELECT *
FROM Order AS o
INNER JOIN OrderDetail AS od ON do.OrderNo = o.OrderNo
INNER JOIN Products AS p ON p.ProductID = od.ProductID

E 12°55'05.63"
N 56°04'39.26"

Thank u Peso , but wat i want is to take rows from Products Table Not From Order Table. Order Table is to Just filter Products in OrderDetail Table by CustID giving in where condition.

vinoop
Starting Member

4 Posts

Posted - 2008-11-19 : 07:28:18

For Example ,
Select Product.*,OrderDetail.* from Product left outer join orderDetail on Product.ProductId=OrderDetail.ProductId innerJoin Order on Order.OrderNo=OrderDetail.OrderNo where Order.CustId=?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 10:14:38

did you mean this?

Select Product.*,OrderDetail.* from Product 
left outer join 
(SELECT * FROM orderDetail 
innerJoin Order on Order.OrderNo=OrderDetail.OrderNo
)t
on Product.ProductId=t.ProductId  
and t.CustId=yourvalue

vinoop
Starting Member

4 Posts

Posted - 2008-11-21 : 00:14:27

thank u visakh16.

But i want to get all the products from Product table(that may present or not present in orderDetail Table for particular Customer). If a product is present in orderdetail then it should return Product.*,OrderDetail.Price or if a Product is not Present in OrderDetail the it should return Product.*,OrderDetail.Price(with null values in it)...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 00:20:37

quote:
Originally posted by vinoop

thank u visakh16.

But i want to get all the products from Product table(that may present or not present in orderDetail Table for particular Customer). If a product is present in orderdetail then it should return Product.*,OrderDetail.Price or if a Product is not Present in OrderDetail the it should return Product.*,OrderDetail.Price(with null values in it)...

Not sure why you belive the solution provided is different. it will give you what you have asked for. have you tried it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources