How to select by comparing two columns

Question

Considering the following structure:

   Coluna_A   | Coluna_B |
01 João       | Carro    | 
02 João       | Moto     |
03 Maria      | Moto     |
04 José       | Carro    |
05 Maria      | Moto     |

I’d like to perform a select to bring me the information, according to the Coluna_A and Coluna_B. The result should display only lines whose Coluna_A has more than one value, whereas Coluna_B has a single value, that is, in this example above, the query should display only lines 01 and 02, because John is the only one who has a car and a motorcycle. You will not be able to show Maria who owns two bikes and José who only owns one car

I tried to do in the following structure:

CREATE TABLE ##TBL_TEMP(
    COLUNA_A VARCHAR(30),
    COLUNA_B VARCHAR(30)
)

INSERT INTO ##TBL_TEMP VALUES ('João','Carro')
INSERT INTO ##TBL_TEMP VALUES ('João','Moto')
INSERT INTO ##TBL_TEMP VALUES ('Maria','Moto')
INSERT INTO ##TBL_TEMP VALUES ('José','Carro')
INSERT INTO ##TBL_TEMP VALUES ('Maria','Moto')

SELECT * FROM ##TBL_TEMP
GROUP BY COLUNA_A, COLUNA_B
HAVING NOT COUNT(COLUNA_A) >= 2 AND COUNT(COLUNA_B) = 1

The result obtained is:

    COLUNA_A  | COLUNA_B
01  João      | Carro
02  José      | Carro
03  João      | Moto

I do not know why José is showing, because it does not meet the requirements of Having

Answer 1

I think the solution will be something like this:

SELECT      X.COLUNA_A
        ,   TMP.COLUNA_B
FROM        ##TBL_TEMP  TMP
INNER JOIN  (
                SELECT      COLUNA_A
                        ,   COUNT(DISTINCT(COLUNA_B)) AS CONTADOR
                FROM        ##TBL_TEMP
                GROUP BY    COLUNA_A
                HAVING      COUNT(DISTINCT(COLUNA_B)) >= 2
            )           X   ON X.COLUNA_A = TMP.COLUNA_A