Select without 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

Select without subquery

Author

Topic

chamalsl
Starting Member

3 Posts

Posted - 2008-04-03 : 01:19:07

Hi,

I have two tables. Customers and Orders.
Customers table contains customer id, and customer name columns.
Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.

I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought.
Can I write this sql without using a subquery.

Thanks,
Chamal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 01:31:03

[code]SELECT o.*,c.customername
FROM Orders o
INNER JOIN Customers c
ON c.customerid=o.customerid
AND o.productid=5[/code]

chamalsl
Starting Member

3 Posts

Posted - 2008-04-03 : 02:41:50

Hi visakh16,

Thanks for ur reply. But this sql will return only the rows where product id is 5,from Orders table.

But all rows for a order should be retuned from Orders table, if the order contains product with id 5.

sample result:-
Name order id product id
John 1 21
John 1 5
John 1 10

thanks,
Chamal.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-03 : 03:22:27

[code]SELECT c.*.
o.*
FROM Customers AS c
INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID
WHERE EXISTS (SELECT * FROM Orders AS x WHERE x.CustomerID = c.CustomerID AND x.ProductID = 5)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-03 : 03:23:31

[code]SELECT c.*.
o.*
FROM Customers AS c
INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID
INNER JOIN (
SELECT CustomerID
FROM Orders
WHERE ProductID = 5
GROUP BY CustomerID
) AS x ON x.CustomerID = c.CustomerID[/code]

E 12°55'05.25"
N 56°04'39.16"

chamalsl
Starting Member

3 Posts

Posted - 2008-04-03 : 03:28:59

Hi Peso,

Thanks for the reply. But I want to get the results without using a subquery (without inner select statement).
Because I have to use this sql in an indexed view.

Thanks,
Chamal.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-03 : 04:06:34

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources