I thought that this would be rather simplistic and straight forward, however, I am having a hard time getting the desired results.
I have a query as such:
COUNT(PM.Planet_Moon_Key) AS Moon_Count
COUNT(PS.Planet_Star_Key) AS Star_Count
FROM dbo.Planet AS P
LEFT OUTER JOIN dbo.Planet_Moon AS PM
ON PM.Planet_Key = P.Planet_Key
LEFT OUTER JOIN dbo.Planet_Star AS PS
ON PS.Planet_Key = P.Planet_Key
WHERE P.Planet_Name LIKE @Planet_Name + '%'
I am trying to get the name of the planet, as well as a count of how many stars and moons that are associated with the planet. Each moon is a record, each star is a record.
My Moon count seems to be correct, though the Star count is the same as my Moon count for some reason.
PS: I tried to make this as realistic as possible, so hopefully the Planet/Moon/Star setup makes it easier
Unless it's not possible by any other means, I tend to avoid usage of DISTINCT as there's generally a way to limit the result set to that of what you actually.
In most cases, a subquery can be replaced by a LEFT JOIN, and in fewer cases in which a LEFT JOIN will not solve the issue, you can toss in a CROSS/OUTER APPLY.
It's just a matter of trying to avoid, but again, if there's no other solution then it's what has to be done, and why I'm asking as nothing that I see as "optimal" or the best way to do it, immediately comes to mind.
@Tonkuma, no I did not run the query, I was just trying to make up a descriptive example without generic tableA.id = tableB examples.