Bobski Bobski - 3 months ago 18
SQL Question

SQL Server : Select Inner Join

I'm trying to build a query to give me some information doing an inner join on two tables,

tableA
and
tableB
. Here's what they look like:

TableA:

ClientID RevNo RevPurp
------------------------------
123 4557 1
124 4555 1
123 6574 2
123 7857 3
124 8987 2


TableB:

RevNo ClientID Active
------------------- -------------
4557 123 True
6574 123 True
7857 123 True
8987 124 True
4555 124 True


Here is what I'm trying to do. I'm trying to do...

Select Distinct ClientID
From TableB
Inner Join TableA On tableB.RevNo = tblB.RevNo
Where RevPurp is not 3.


RevPurp
can be 1,2,3 - I'm only trying to select those distinct clients that can have either 1,2 but cannot have a 3.

Looking at the data in the table I should only have 1 record....

124


Because client 123 has RevPurp 3. So if a client has RevPurp 3, I do not want to see that client when I execute the query!

EDIT - I added a field in TableB called ACTIVE. I need to make sure Active = True when doing my query!

Answer

as @devlin mentioned in a comment, you do not need tableB to do this: You want a list of unique (distinct) ClientIds in TableA where there is no record in tableA with a RevPurp value of 3.

EDIT: adding in the filter predicate on TableB.Active

Select distinct a.ClientId 
from tableA a join tableb b 
   on b.RevNo = a.RevNo
Where b.Active = 1  -- <===== is Active a BIT field
  and Not exists
   (Select * from tableA 
    Where ClientId = a.ClientId 
       and RevPurp = 3)