开发者

Adding an INNER JOIN to a query should not increase the number of rows returned right?

开发者 https://www.devze.com 2023-01-12 05:22 出处:网络
I have a query like the following that returns the correct number of rows that I would expect. (It has to match a similar query that returns the same data sets but with different associated info from

I have a query like the following that returns the correct number of rows that I would expect. (It has to match a similar query that returns the same data sets but with different associated info from other related tables.

SELECT *
FROM LK
INNER JOIN STC ON LK.V = STC.VI
LEFT OUTER JOIN BC ON LK.BC = BC.ID
LEFT OUTER JOIN K AS LT ON ISNULL(BC.ZA, LK.VH) = LT.KNN
WHERE
    LT.KI IS NOT NUL开发者_运维知识库L AND LT.KS = 0
  OR 
    LT.KI IS NULL 
ORDER BY
  LK.Z

But as soon as I add other inner joins I actually get more rows back. I thought an inner join only returns rows when information is found in both sides of the join, so I expected to get either the same or less rows back. But I get around twice as much.

For example:

SELECT *
FROM LK
INNER JOIN STC ON LK.V = STC.VI

INNER JOIN VK ON LK.V = VK.ID
INNER JOIN K AS A ON VK.AIN = A.KNN

LEFT OUTER JOIN BC ON LK.BC = BC.ID
LEFT OUTER JOIN K AS LT ON ISNULL(BC.ZA, LK.VH) = LT.KNN
WHERE
    LT.KI IS NOT NULL AND LT.KS = 0
  OR 
    LT.KI IS NULL 
ORDER BY
  LK.Z

Does this make sense? How can adding two more inner joins result in more rows being returned?

And more to the point of my actual problem, how I can adjust the second query so it returns the same rows as the first query but with the extra columns from the joined tables?


if there's more than one VK per LK then it will increase the # of rows. I don't understand your schema enough to fix it though.

0

精彩评论

暂无评论...
验证码 换一张
取 消