I'm running MySQL version 5.0.33.
I have the 2 following queries:
SELECT
Resource.Id,
Resource.Name,
Resource.Description,
Resource.IsVisible,
Resource.UpdatedDate,
Resource.CreatedDate
FROM
Resource
INNER JOIN
ObjectTarget ON
Resource.Id = ObjectTarget.TargetId AND ObjectTarget.TargetType = 'JOE'
WHERE
ObjectTarget.ObjectType = 'BOB'
AND
ObjectTarget.ObjectId = 1
AND
Resource.IsVisible = 'Y'
ORDER BY Resource.Name asc
and
SELECT
OT1.TargetType,
OT1.TargetId,
Resource.Id,
Resource.Name,
Resource.Description,
Resource.IsVisible,
Resource.UpdatedDate,
Resource.CreatedDate
FROM
Resource
INNER JOIN
ObjectTarget ON
Resource.Id = ObjectTarget.TargetId AND ObjectTarget.TargetType = 'JOE'
LEFT OUTER JOIN
ObjectTarget AS OT1 ON
OT1.ObjectType = 'JOE' AND OT1.ObjectId = ObjectTarget.TargetId
WHERE
ObjectTarget.ObjectType = 'BOB'
AND
ObjectTarget.ObjectId = 1
AND
Resource.IsVisible = 'Y'
ORDER BY Resource.Name asc
The only difference between them is that the second query includes 2
additional fields (from the OUTER JOINed table) and also the LEFT OUTER
JOIN. When run, the first query returns 77 rows of distinct resources.
When run, the second query returns 180 rows but within that data set there
are only 32 distinct resources.
Now, my understanding of OUTER JOINs says that all of the rows from the
original query would be returned (77) along with any (if any) related data
from the OUTER JOINed table. So if there was no related data in the OUTER
JOINed table for the current row, OT1.TargetType and OT1.TargetId would
simply be NULL. But if there was related data, there would be an additional
row in the resultant data set for each related data row.
My expectation is that the second query should have returned the same 77
distinct resources from the first query along with any additional rows
correlating to the relating data from the OUTER JOINed table. But that's
not what's happening.
Where am I going wrong here?
thnx,
Christoph
