SQL inner join on column A and compare values in column B

I am sure or maybe hope there is a simple way to do this. Below is my latest attempt. The query should return only one row, but I get 31.

Query:

    `select distinct a.[EC CERS], a.[EC Tank ID], [CIW Tank ID]
    from #tempectank a  
    inner join #tempciwtank b on a.[EC CERS] = b.[CIW CERS] 
    where a.[EC Tank ID] <> b.[CIW Tank ID]`

Table Data:

Table A

EC CERS	EC Tank ID
100338	1
100338	2
100338	3

Table B

CIW CERS	CIW Tank ID
100338	001
100338	2
100338	3

Table Schema

    create table #tempectank(
    s int IDENTITY (1, 1),
    [EC CERS] varchar(9),
    [EC Tank ID] varchar(255)
    )

I need the query to return one row preferably showing the EC CERS, EC Tank ID and CIW Tank ID.

Answer

if it is the matter of leading ziros , you can go with this solution:

this is not going to work if you have non-numeric in your Ids

Select a.[EC CERS], a.[EC Tank ID], b.[CIW Tank ID]
from #tempectank a  
inner join #tempciwtank b 
    on a.[EC CERS] = b.[CIW CERS] 
    and a.[EC Tank ID] <> b.[CIW Tank ID]
    and cast(a.[EC Tank ID] as int ) =  cast(b.[CIW Tank ID] as int)

actually another solution is to show the ones that you can’t find a match:

select * from #tempectank a   
where not exists (select 1 from #tempciwtank b 
                   where a.[EC CERS] = b.[CIW CERS] 
                     and a.[EC Tank ID] = b.[CIW Tank ID]
                 )
and exists (select 1 from #tempciwtank b where a.[EC CERS] = b.[CIW CERS])

if you want to show non-matched cases side by side, you have to go from both side and find non-matching instances :

select * from (
select * from #tempectank a   
where not exists (select 1 from #tempciwtank b 
                   where a.[EC CERS] = b.[CIW CERS] 
                     and a.[EC Tank ID] = b.[CIW Tank ID]
                 )
and exists (select 1 from #tempciwtank b where a.[EC CERS] = b.[CIW CERS])
) a 
join (
select * from #tempciwtank b 
where not exists (select 1 from #tempectank a   
                   where a.[EC CERS] = b.[CIW CERS] 
                     and a.[EC Tank ID] = b.[CIW Tank ID]
                 )
and exists (select 1 from #tempectank a  where a.[EC CERS] = b.[CIW CERS])
)b 
on  a.[EC CERS] = b.[CIW CERS]