Sql Server 2008 - Inner Join

Question

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

Accepted Answer

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
      ) ;

Answer 2

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)

Answer 3

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

Answer 4

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

Sql Server 2008 - Inner Join

4 Answers

Must Read

Your Answer

Marcus Vance

Sql Server 2008 - Inner Join

4 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Marcus Vance