why I get different number of records when using LEFT JOIN

Question

I need to join many to many relationship. In order to do that I am using

LEFT OUTER JOIN  
    (SELECT 
         SICCode, QuoteID 
     FROM  
         NetRate_Quote_Insur_Quote_Busin 
     WHERE 
         QuoteID IN 
              (SELECT DISTINCT QuoteID 
               FROM NetRate_Quote_Insur_Quote_Busin)) nr  ON nr.QuoteID = tblQuotes.QuoteID

The goal is to bring the column SICCode. For that, I am using DISTINCT statement to make relationship many to one. But for some reason I got different number of records.

The last LEFT OUTER JOIN is the one that I am stuck with

Full query looks like this:

SELECT      
    MONTH(INV.EffectiveDate) AS Effective_Month,
    tblQuotes.PolicyNumber,
    YEAR(INV.EffectiveDate) AS Effective_Year, 
    INV.EffectiveDate,
    INV.[InvoiceDate] as [Billed Date],
    tblQuotes.ExpirationDate as [Policy Expiration Date],
    INV.DueDate,
    dbo.tblFin_InvoiceDetails.AmtBilled AS Written
FROM  
    tblClientOffices
INNER JOIN 
    tblInsureds (NOLOCK) 
INNER JOIN 
    tblFin_Invoices INV
INNER JOIN 
    tblFin_InvoiceDetails ON INV.InvoiceNum = dbo.tblFin_InvoiceDetails.InvoiceNum
INNER JOIN 
    tblCompanyLines (NOLOCK) ON dbo.tblFin_InvoiceDetails.CompanyLineGuid = dbo.tblCompanyLines.CompanyLineGUID 
INNER JOIN 
    lstLines (NOLOCK) ON dbo.tblCompanyLines.LineGUID = dbo.lstLines.LineGUID 
                      AND dbo.tblCompanyLines.LineGUID = dbo.lstLines.LineGUID
INNER JOIN 
    tblProducerLocations (NOLOCK) ON INV.ProducerLocationGUID = tblProducerLocations.ProducerLocationGUID 
    ON tblInsureds.InsuredGUID = INV.InsuredGUID
INNER JOIN 
    tblQuotes (NOLOCK) ON INV.QuoteID = tblQuotes.QuoteID 
                       AND INV.QuoteControlNum = tblQuotes.ControlNo
INNER JOIN 
    lstPolicyTypes (NOLOCK) ON tblQuotes.PolicyTypeID = lstPolicyTypes.PolicyTypeID
INNER JOIN 
    tblSubmissionGroup (NOLOCK) ON tblQuotes.SubmissionGroupGuid = tblSubmissionGroup.SubmissionGroupGUID
INNER JOIN 
    tblCompanyLocations (NOLOCK) ON tblQuotes.CompanyLocationGuid = tblCompanyLocations.CompanyLocationGUID
INNER JOIN 
    tblUsers (NOLOCK) ON INV.UnderwriterUserGUID = tblUsers.UserGUID 
    ON tblClientOffices.OfficeGUID = tblQuotes.IssuingLocationGuid
LEFT OUTER JOIN 
    tblUsers tblUsers_1 (NOLOCK) ON tblSubmissionGroup.InHouseProducerUserGuid = tblUsers_1.UserGUID
LEFT OUTER JOIN  
    (SELECT SICCode, QuoteID 
     FROM NetRate_Quote_Insur_Quote_Busin 
     WHERE QuoteID IN 
           (SELECT DISTINCT QuoteID 
            FROM NetRate_Quote_Insur_Quote_Busin)) nr ON nr.QuoteID = tblQuotes.QuoteID
WHERE 
    (tblInsureds.TestInsured = 0)
    AND (INV.Failed = 0)
    AND (tblFin_InvoiceDetails.ChargeType = 'p')
ORDER BY    
    YEAR(INV.EffectiveDate), 
    MONTH(INV.EffectiveDate), 
    lstLines.LineID

What would be the trick to bring Column SICCode and keep the same number of records?
I also tried to do this:

LEFT OUTER JOIN NetRate_Quote_Insur_Quote_Busin nr  ON nr.QuoteID = 
                            (
                            SELECT distinct QuoteID from NetRate_Quote_Insur_Quote_Busin
                            where QuoteID =tblQuotes.QuoteID
                            )

But it gives me more records than it should be

Also tried this one:

    LEFT OUTER JOIN 
                        (   SELECT TOP 1 SICCode, QuoteID 
                            FROM NetRate_Quote_Insur_Quote_Busin 
                            WHERE QuoteID IN 
                                            (SELECT DISTINCT QuoteID 
                                                FROM NetRate_Quote_Insur_Quote_Busin)order by QuoteID desc ) nr ON nr.QuoteID = tblQuotes.QuoteID

But doesnt bring me any SICCode

You get a different number of rows, because the query inside the `LEFT JOIN` returns more than one row per `QuoteID` for some values of `QuoteID` — Lamak, Nov 04 '16 at 15:35
This appears to be a financial application. I would strongly urge you to stop littering your queries with NOLOCK. When you need accurate results this query hint will ensure that you have mostly correct information most of the time. It can and will return missing and/or duplicate rows. There is whole list of other sinister things this query hint will do. Far more than simply dirty reads. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Nov 04 '16 at 15:38
Would a TOP(1) SICCode work here, meaning only return the first SICCode for the quote? Or do you need a master record per code? — Ross Bush, Nov 04 '16 at 15:39
For some reason TOP(1) does not bring me any SICCode. All NULL's — Serdia, Nov 04 '16 at 15:53
If you use TOP then make sure you use an ORDER BY to keep it deterministic (reproducible) — S3S, Nov 04 '16 at 16:30

score 2 · Accepted Answer · answered Nov 04 '16 at 16:45

2

DISTINCT will not change a many to many relationship to a many to one. It looks like your data has many SICCode values to one QuoteID which is why you getting higher record counts.

You could use a window function like Rank, but this is an arbitrary selection.

You could try to pivot/unpivot the SICCode column, but that would give you 1 column for each possible value, which would not work if you have a large number of unique values in the column.

A final option is to create a new table which has a QuoteID column and a SICCodes column, then populate it withSICCodes being a comma seperated list of the codes.

In conclusion, there is no easy way to change a many to many relationship to a many to one relationship unless you are willing to lose data or there are only a few values.

answered Nov 04 '16 at 16:45

Trisped

5,705
2
45
58

I just look at the data, seems like same `SICCode` can be for multiple `QuoteID`. But the `QuoteID` will be different. But not many `SICCode` values to one `QuoteID` – Serdia Nov 04 '16 at 17:00
@Oleg sounds like the comma separated list option is a good one for what you are trying to do. You will be able to see all possible options without changing the row count. – Trisped Nov 04 '16 at 17:13
Thanks. Could you please point me to the good examples? Thanks – Serdia Nov 04 '16 at 17:25
You could try [XPATH](http://stackoverflow.com/a/545672/641833). It is probably easier than the solution I was thinking of. You will get HTML encoded results, so you might have to fix any < > & characters. – Trisped Nov 04 '16 at 17:53
@Oleg Other XPATH examples: http://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value , http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server . Other options: http://www.sqlservercentral.com/Forums/Topic1260952-338-1.aspx , http://www.sqlmatters.com/Articles/Converting%20row%20values%20in%20a%20table%20to%20a%20single%20concatenated%20string.aspx – Trisped Nov 04 '16 at 18:03

why I get different number of records when using LEFT JOIN

1 Answers1