Joining multiple tables in SQL - Best practise

Question

I have 3 tables.

Table1 : ContractMain

ContractNo      SDValue Value   CityId     AreaCode
C0001           840888  18482   CY00004    AR0001
C0002           133500  35000   CY00004    AR0001
C0003           NULL    4200    CY00004    AR0001
C0004           73440   7400    CY00004    AR0001
C0005           73440   7400    CY00005    AR0002

Table2 : ContractDetails

ContractNo  Code    TxPr Amt
C0001       1       25   2102220.50
C0001       2       25   2102220.50
C0001       3       25   2102220.50
C0002       1       25   333750.00
C0002       2       25   333750.00
C0003       1       25   333750.00
C0003       2       25   333750.00

Table3 : City

CityID  CName
CY00004 AMR
CY00005 EEE
CY00006 TRE
CY00008 WSE

Table4 : Area

AreaCode AName
AR0001   COK
AR0002   TCR
AR0003   EKM
AR0004   RTT

My expected result is

 ContractNo     SDValue  Value  CityId     AreaCode   Amt  CName  AName

Script I have written

select  A.*,B.Amt,C.CName,D.AName 
from ContractMain A  INNER JOIN ContractDetails B on  A.ContractNo=B.ContractNo 
Inner join City C on C.CityId=A.CityId
Inner join Area D on D.AreaCode=A.AreaCode

Here I get repeated values against a ContractNo because in Table B we have got different values for column Code

I need to get distinct values for ContractNo. Which is the best way to achieve this

Answer 1

Here's your query. used left join to ContractDetails in case there's no contract details entered for a new created contract.

select   ContractNo
    ,SDValue
    ,[Value]
    ,CityId
    ,AreaCode
    ,B.Amt
    ,(Select top 1 CName from City C where C.CityId=A.CityId) as CName
    ,(Select top 1 AName from Area D where D.AreaCode=A.AreaCode) as AName
from ContractMain A  
left join
    (select ContractNo, sum(Amt) as Amt ContractDetails group by ContractNo) B on  A.ContractNo=B.ContractNo