Matching ID if more than one in column then nothing-CodePudding

I have table1 and table2
table1

nameID   column1  column2  column3
Joe      10       10       99

table2

nameID  column1  cost 
Joe      99       100
Joe      10       100
Joe      30       200

My goal to have column3 from table1 match with column1 from table2.
IF table2 have 2 or more records even if table1 column3 match with table2 column1 (99) and expect nothing output.
IF table 2 have only one record on column1 (99) match with table1 column3 (99) then expect result

nameID   column3  cost
Joe      99         100

I have tried

select t1.name, t1.column3
from table1 t1 
join table2 t1 on t1.nameID = t2.nameID
where 
t1.column3 = t2.column1
and t1.column1 <> t2.column1
and t1.column2 <> t2.column1

Not sure how to make it works. Thank you.

CodePudding user response：

WITH CTE_X
AS (
    SELECT count(*) AS countX
    FROM table2 t2
    )
SELECT table1.nameId
    ,table1.column3
FROM table1
INNER JOIN table2 ON table1.column3 = table2.column1
JOIN CTE_X ON 1 = 1
WHERE CTE_X.countX = 1

Please see this