SQL Server: expression in "IN" operator

Question

I have table of shops name, ID, Type,etc. Also I have classes of shops (supermarkets, store, retailer) and each class has several types (supermarket = megamarket, multimarket, etc) I would like to get all shops which are supermarkets. Let's assume supermarkets is ShopClass 2.

So I would like to write query like this:

SELECT ShopID, ShopName 
FROM Shops 
WHERE ShopType in (SELECT ShopType FROM ShopClasses WHERE ShopClass = 2)

But unfortunately that query doesn't work:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

What is the reason? AFAIK I can use expressions in IN statement.

marc_s · Accepted Answer · 2012-07-08 18:58:53Z

1

Try this:

SELECT ShopID, ShopName 
FROM Shops AS shopslist 
WHERE shopslist.ShopType in (SELECT ShopType FROM ShopClasses WHERE ShopClass = 2)

edited Jul 8, 2012 at 18:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 8, 2012 at 18:05

Richard Grossman

7311 gold badge5 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

seeker Over a year ago

Thanks, it works now. Quite strange, but that is the exact solution.

marc_s Over a year ago

If you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

Collectives™ on Stack Overflow

SQL Server: expression in "IN" operator

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related