September 26, 2018 at 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
September 26, 2018 at 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.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 26, 2018 at 8:15 am
mediacommentry - Wednesday, September 26, 2018 7:05 AMHi,I have a View, [ViewName] when I select from this view I get 10 rows back.
Now I LEFT JOIN another ViewSelect 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
sgmunson - Wednesday, September 26, 2018 7:12 AMThat 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
mediacommentry - Wednesday, September 26, 2018 11:54 PMsgmunson - Wednesday, September 26, 2018 7:12 AMThat 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