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.
精彩评论