where c.asset_id = v.parent_asset_id and (c.asset_type,d.asset_type,v.assoc_type) in (select m.asset_key,n.asset_key,o.association_key from dbo.t_association_rule o, dbo.t_asset_master m, dbo.t_asset_master n)
SQL Server does not support using the IN operator in that way, that is, evaluating whether a set of two or more columns are "in" a sub-query. I have a couple of solutions which I believe accomplish the same thing. One uses an inner join, while the other involves using the EXISTS operator and a correlated sub-query. Using an employee table as an example, here's the first:
select e.FirstName, e.LastName from Emp e inner join OtherEmp oe on ( oe.FirstName = e.FirstName and oe.LastName = e.LastName );Here's the second solution:
select e.FirstName, e.LastName from Emp e where exists ( select * from OtherEmp oe where oe.FirstName = e.FirstName and oe.LastName = e.LastName );That fact that in your particular situation, you use columns from different tables should not affect the effectiveness of these two approaches.
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2002