Multiple-Column Subquery in Transact-SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple-Column Subquery in Transact-SQL?

Author

Topic

Brouda
Starting Member

2 Posts

Posted - 2004-11-04 : 07:14:28

Is there a way to use multiple-column subqueries in transact-sql?

I know it's possible in Oracle, but in SQL Server 2000 it doesn't seem to work. (error: Incorrect syntax near ',')

e.g.: SELECT order_id, product_id, quantity
FROM item
WHERE (product_id, quantity) IN (
SELECT product_id, quantity FROM item WHERE order_it = 200)
AND order_id = 200;

Any help is greatly appriciated.

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-04 : 08:01:24

SELECT order_id, product_id, quantity
FROM item t1
WHERE exists (select * from item t2 WHERE t2.order_it = 200
AND t2.order_id = 200 and t1.product_id = t2.product_id and t1.quantity = t2.quantity)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-04 : 09:13:34

you can also do an INNER JOIN, but make sure you join to only distinct combos of your columns otherwise it may cause duplicate results:


SELECT 
  order_id, product_id, quantity
FROM 
  item
INNER JOIN
  (SELECT DISTINCT Product_Id, Quantity FROM item WHERE order_Id=200) A
ON
  Item.Product_ID = a.Product_Id and Item.Quantity = A.Quantity
WHERE
  Order_ID = 200

Though, of course, I hope this is a REALLY silly example because if you are writing WHERE clauses like this then you really need to rewrite them. That's pretty convoluted logic when all you need is:

select order_id, product_id, quantity from item t2 WHERE t2.order_it = 200

But then again, coming from an Oracle background, there's no telling what kinds of bad habits you might have picked up !

- Jeff

Brouda
Starting Member

2 Posts

Posted - 2004-11-05 : 13:34:14

Thank you all for your replies. My code works perfectly now.

I know my example was quite silly.

I wanted to keep it simple.

Brouda.

bhaskarareddy
Starting Member

7 Posts

Posted - 2012-06-28 : 23:46:57

hi

it will help you with example

[url]http://csharpektroncmssql.blogspot.com/2012/01/multiple-row-subqueries.html[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources