CODEWITHSUNDEEP
sql-server-2008inner-join
Abid Ali
Abid Ali

Reputation: 1747

Getting Repeated values in SQL

I was desperately trying harder and harder to get this thing done but didn`t yet succeed. I am getting repeated values when i run this query. 

 select 
    tbl_ShipmentStatus.ShipmentID
    ,Tbl_Contract.ContractID,
    Tbl_Contract.KeyWinCountNumber,
    Tbl_Item.ItemName,
    Tbl_CountryFrom.CountryFromName,
    Tbl_CountryTo.CountryToName,
    Tbl_Brand.BrandName,
    Tbl_Count.CountName,
    Tbl_Seller.SellerName,
    Tbl_Buyer.BuyerName, 
    Tbl_Contract.ContractNumber,
    Tbl_Contract.ContractDate,
    tbl_CountDetail.TotalQty,
    tbl_CostUnit.CostUnitName,
    tbl_Comission.Payment,
    tbl_Port.PortName,
    Tbl_Contract.Vans,
    tbl_Comission.ComissionPay,
    tbl_Comission.ComissionRcv,
    tbl_CountDetail.UnitPrice,
    tbl_Comission.ComissionRemarks,
    tbl_CountDetail.Amount,
    tbl_LCStatus.LCNumber,
    tbl_ShipmentStatus.InvoiceNumber,
    tbl_ShipmentStatus.InvoiceDate,
    tbl_ShipmentStatus.BLNumber,
    tbl_ShipmentStatus.BLDate,
    tbl_ShipmentStatus.VesselName,
    tbl_ShipmentStatus.DueDate
     from tbl_ShipmentStatus 

    inner join tbl_LCStatus
    on
    tbl_LCStatus.LCID = tbl_ShipmentStatus.LCStatusID

    inner join Tbl_Contract
    on
    tbl_LCStatus.ContractID = Tbl_Contract.ContractID 

    inner join Tbl_CountDetail
    on Tbl_Contract.ContractID = Tbl_CountDetail.ContractId

    inner join tbl_Comission
    on
    tbl_Comission.ContractID = Tbl_Contract.ContractID

    inner join Tbl_Item
    on
    Tbl_Item.ItemID = Tbl_Contract.ItemID

    inner join Tbl_Brand
    on Tbl_Brand.BrandID = Tbl_Contract.BrandID
    inner join Tbl_Buyer
    on Tbl_Buyer.BuyerID = Tbl_Contract.BuyerID
    inner join Tbl_Seller
    on Tbl_Seller.SellerID = Tbl_Contract.SellerID
    inner join Tbl_CountryFrom
    on Tbl_CountryFrom.CountryFromID = Tbl_Contract.CountryFromID
    inner join Tbl_CountryTo
    on
    Tbl_CountryTo.CountryToID = Tbl_Contract.CountryToID
    inner join Tbl_Count
    on
    Tbl_Count.CountID = Tbl_CountDetail.CountId
    inner join tbl_CostUnit
    on tbl_Comission.CostUnitID = tbl_CostUnit.CostUnitID
    inner join tbl_Port
    on tbl_Port.PortID = tbl_Comission.PortID

    where tbl_LCStatus.isDeleted = 0
    and tbl_ShipmentStatus.isDeleted =0
    and tbl_LCStatus.isDeleted = 0
    and Tbl_CountDetail.isDeleted = 0
    and Tbl_Contract.isDeleted = 0

    and tbl_ShipmentStatus.LCStatusID = 5

I have also attached a picture of my result set of rows. Any suggestions why this is happening would really be appreciable.

Result Set

Upvotes: 0

Views: 73

Answers (2)

Bacon Bits
Bacon Bits

Reputation: 32155

Typically this happens when you have an implicit partial cross join (Cartesian product) between two of your tables. That's what it looks like to me here.

This happens most often when you have a many-to-many relationship. For example, if a single Album allows both multiple Artists and multiple Songs and the only relationship between Artists and Songs is Album, then there's essentially a many-to-many relationship between Artists and Songs. If you select from all three tables at once you're going to implicitly cross join Artists and Songs, and this may not be what you want.

Looking at your query, I see many-to-many between Tbl_CountDetail and tbl_Comission through Tbl_Contract. Try eliminating one of those joins to test to see if the behavior disappears.

Upvotes: 1

cjds
cjds

Reputation: 8426

Try using the DISTINCT keyword. It should solve your issue

Select DISTINCT ....

Wait as far as I can see your records are not duplicates.

HOWEVER

Notice the CountName column and Shipment ID column

The combination is unique for every row. Hence the values are unique as far as I can see. Try not selecting CountName.

Well if you have distinct rows its not a duplication problem. The issue is during the join a combination is occurring you don't want it to duplicating the results.

Either don't select CountName or you have a mistake in your data.

Only one of those rows should be true either 6 with Count2 or 6 with Count1. Likewise for 7. The fact that your getting both when your not supposed to indicates a logic mistake

Upvotes: 0

Related Questions