SQL Server - Incorrect syntax near )

Question

Can you please review this code? I got this error and I'm not sure what's causing it:

Incorrect syntax near ')'.

select * 
from
    (select distinct 
         sar90.code, sar90.state, sar90.county, 
         sabet.code, sabet.state, sabet.county 
     from
         [dbo].[sarshomari_90] as sar90, 
         [dbo].[Fixed] as sabet 
     where 
         sar90.county = sabet.county 
         and sar90.state = sabet.state 
         and sar90.state = N'kerman')

Answer 1

You need to alias your subquery. However, you do not need to use a subquery for this. You can use SELECT DISTINCT directly. Also, please avoid using old-style JOIN syntax and use an explicit JOIN declaration instead.

However, if you wish to use subquery, your column must have unique names. Do this by adding unique aliases.

select *
from(
    select distinct
        sar90.code as code1, 
        sar90.state as state1,
        sar90.county as country1,
        sabet.code as code2,
        sabet.state as state2,
        sabet.county as country2
    from [dbo].[sarshomari_90] as sar90
    inner join [dbo].[Fixed] as sabet 
        on sar90.county = sabet.county 
        and sar90.state = sabet.state 
    where
        sar90.state = N'kerman'
)t