CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008t-sql
MMomani
MMomani

Reputation: 43

Get DISTINCT record using INNER JOIN

I have the follwong Query on multi tables 

SELECT DISTINCT b.BoxBarcode as [Box Barcode], (select case when  b.ImagesCount IS NULL
        then 0 
        else  b.ImagesCount end) as [Total Images], s.StageName as [Current   Stage] ,d.DocuementTypeName as [Document Type],
        u.UserName as [Start User],uu.UserName as [Finished User]

FROM [dbo].[Operations] o
    inner join dbo.LKUP_Stages s on 
        o.stageid=s.id
    inner join dbo.LKUP_Users u on
        u.id=o.startuserid
    left join dbo.LKUP_Users uu on
        uu.id=o.FinishedUserID
    inner join boxes b on
        b.id=o.boxid
    inner join LKUP_DocumentTypes d on
        d.ID = b.DocTypeID 

where b.IsExportFinished = 0

when i select count from the Boxes table where IsExportFinished = 0 i got the Count 42 records, when i run the above qoury i got 71 records, i want just the 42 records in Boxes table to retrived.

Upvotes: 2

Views: 7293

Answers (3)

Devart
Devart

Reputation: 121912

Try this one -

SELECT
      Box_Barcode = b.BoxBarcode
    , Total_Images = ISNULL(b.ImagesCount, 0)
    , Current_Stage = s.StageName
    , Document_Type = d.DocuementTypeName
    , Start_User = u.UserName
    , Finished_User = uu.UserName
FROM (
    SELECT DISTINCT 
          o.stageid
        , o.boxid
        , o.startuserid
        , o.FinishedUserID
    FROM dbo.[Operations]
) o
JOIN dbo.LKUP_Stages s ON o.stageid = s.id
JOIN dbo.boxes b ON b.id = o.boxid
JOIN dbo.LKUP_DocumentTypes d ON d.id = b.DocTypeID
JOIN dbo.LKUP_Users u ON u.id = o.startuserid
LEFT JOIN dbo.LKUP_Users uu ON uu.id = o.FinishedUserID
WHERE b.IsExportFinished = 0

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

You are doing a one-to-many join, i.e. at least one of the tables have multiple rows that match the join criteria.

Step one is to find which table(s) that give the "duplicates".

Once you have done that you may be able to fix the problem by adding additional criteria to the join. I'm taking a guess that the same boxid occurs several times in the Operations table. If that is the case you need to decide which Operation row you want to select and then update the SQL accordingly.

Upvotes: 4

Antoniossss
Antoniossss

Reputation: 32507

I guess that if you change your LEFT JOIN into INNER JOIN you will get 42 records as requested.

Upvotes: 0

Related Questions