Post reply

LEFT JOIN

  • September 26, 2018 at 7:05 am

    #408550

    Hi,

    I have a View, [ViewName] when I select from this view I get 10 rows back.
    Now I LEFT JOIN another View  Select a.Field1, a.Field2, a.Field3 FROM ViewName a LEFT JOIN ViewNameTwo b ON a.Field1 = b.Field1
    then now I'm getting 15 rows back. I need to understand why, my ViewName is my driving dataset, I think LEFT JOIN should only return rows from my driving dataset and not add more rows. And my JOINING field, Field1 is not returning any NULL values. please help

  • September 26, 2018 at 7:12 am

    #2007069

    That 2nd view is the cause of this.   You must have multiple rows in the 2nd view that match on the JOIN condition of your LEFT JOIN for at least 1 of the rows in the original view.   Try the following query:
    WITH MultipleRows AS (

        SELECT a.Field1, COUNT(*) AS Row_Count
        FROM View1 AS a
        LEFT OUTER JOIN View2 AS b
            ON a.Field1 = b.Field1
        GROUP BY a.Field1
        HAVING COUNT(*) > 1
    )
    SELECT V2.*
    FROM MultipleRows AS MR
    INNER JOIN View2 AS V2
        ON MR.Field1 = V2.Field1
    ORDER BY MR.Field1;

    This should identify those rows.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • September 26, 2018 at 8:15 am

    #2007090

    mediacommentry - Wednesday, September 26, 2018 7:05 AM

    Hi,

    I have a View, [ViewName] when I select from this view I get 10 rows back.
    Now I LEFT JOIN another View  Select a.Field1, a.Field2, a.Field3 FROM ViewName a LEFT JOIN ViewNameTwo b ON a.Field1 = b.Field1
    then now I'm getting 15 rows back. I need to understand why, my ViewName is my driving dataset, I think LEFT JOIN should only return rows from my driving dataset and not add more rows. And my JOINING field, Field1 is not returning any NULL values. please help

    If your table's joining conditions contains duplicate then you will get more records which is actual behavior.

    Saravanan

  • September 26, 2018 at 11:54 pm

    #2007188

    sgmunson - Wednesday, September 26, 2018 7:12 AM

    That 2nd view is the cause of this.   You must have multiple rows in the 2nd view that match on the JOIN condition of your LEFT JOIN for at least 1 of the rows in the original view.   Try the following query:
    WITH MultipleRows AS (

        SELECT a.Field1, COUNT(*) AS Row_Count
        FROM View1 AS a
        LEFT OUTER JOIN View2 AS b
            ON a.Field1 = b.Field1
        GROUP BY a.Field1
        HAVING COUNT(*) > 1
    )
    SELECT V2.*
    FROM MultipleRows AS MR
    INNER JOIN View2 AS V2
        ON MR.Field1 = V2.Field1
    ORDER BY MR.Field1;

    This should identify those rows.

    Thank you so much.

  • September 27, 2018 at 2:07 pm

    #2007308

    mediacommentry - Wednesday, September 26, 2018 11:54 PM

    sgmunson - Wednesday, September 26, 2018 7:12 AM

    That 2nd view is the cause of this.   You must have multiple rows in the 2nd view that match on the JOIN condition of your LEFT JOIN for at least 1 of the rows in the original view.   Try the following query:
    WITH MultipleRows AS (

        SELECT a.Field1, COUNT(*) AS Row_Count
        FROM View1 AS a
        LEFT OUTER JOIN View2 AS b
            ON a.Field1 = b.Field1
        GROUP BY a.Field1
        HAVING COUNT(*) > 1
    )
    SELECT V2.*
    FROM MultipleRows AS MR
    INNER JOIN View2 AS V2
        ON MR.Field1 = V2.Field1
    ORDER BY MR.Field1;

    This should identify those rows.

    Thank you so much.

    You're very welcome.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply