Guidance on using IN - 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

Guidance on using IN

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-15 : 13:40:52

I have two tables:

Offense_Conversion
OffensePropertyConversion

I want to do a select from Offense_Conversion to show me where the value in columns Offensno exists in Offensepropertyconversion.


SELECT * FROM OffensePropertyConversion WHERE offenseno IN offense_conversion

is a no go.

any guidance is appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-15 : 13:45:03

[code]SELECT * FROM OffensePropertyConversion
WHERE offenseno IN (SELECT offenseno FROM offense_conversion)

-- Better:

SELECT A.* FROM OffensePropertyConversion A
INNER JOIN offense_conversion B ON A.offenseno=B.offenseno

-- Maybe best:

SELECT * FROM OffensePropertyConversion A
WHERE EXISTS(SELECT * FROM offense_conversion WHERE offenseno=A.offenseno)[/code]