SQL Server 2008 - Inner join

Good morning,

I use SQL Server 2008 and I have 2 tables that I join on App#

Structure of T2 is and it has the following values:

App # code --------------- 123 8 123 16 123 32 124 16 125 8 125 16 

I need to print only applications that have a code of 16. My code is as follows:

Select appID, Code from T1 Inner join T2 on T1.AppID = T2.AppID and Code = 16 

However I get a result including app# 123, 124 and 125, but I need only 124 to show (I need to extract only apps that have code of 16 and not something else.)

Thanks for your help Joe

4 Answers

SELECT T1.appID FROM T1 JOIN T2 ON T2.AppID = T1.AppID AND T2.Code = 16 WHERE NOT EXISTS ( SELECT * FROM T2 WHERE T2.AppID = T1.AppID AND T2.Code <> 16 ) ; 

This should also work; Here is Sql-Demo

select T2.appId,code from T2 join T1 on T2.appId = T1.appId where code = 16 and T2.appId not in (select appId from T2 where code != 16) 

Please try the condition in where

select appId, code from ( select T2.appId, code, count(*) over (partition by T2.appId) CNT from T2 join T1 on T2.appId = T1.appId where code=16 )x where CNT=1 

or the same can be done using having

select T2.appId, code from T2 join T1 on T2.appId = T1.appId where code=16 group by T2.appId, code having count(T2.appID)=1 

The condtion on a select statment is where not AND

TRY THIS ONE

Select appID, Code from T1 Inner join T2 on T1.AppID = T2.AppID where t1.code=16