I'm having trouble getting the correct count from a total query. I have 2 tables in a 1-To-Many relationship and I want to count a column in the many table. This query works correctly:
Now I add another table related as another "many" to the same "one" table. I want to count another column on the "many" side. This query doesn't return the correct answer...
The second query returns counts much greater than they should be. Can anyone see what I'm doing wrong?
Thanks,
Adam
Code:
SELECT tbmAdGroups.AdGroupID, tbmAdGroups.AdGroupName, Count(tbmKeyPhrases.KeyPhraseID) AS CountOfKeyPhraseID FROM tbmAdGroups LEFT JOIN tbmKeyPhrases ON tbmAdGroups.AdGroupID = tbmKeyPhrases.AdGroupID WHERE tbmAdGroups.DestinationID=1 GROUP BY tbmAdGroups.AdGroupID, tbmAdGroups.AdGroupName;
Code:
SELECT tbmAdGroups.AdGroupID, tbmAdGroups.AdGroupName, Count(tbmKeyPhrases.KeyPhraseID) AS CountOfKeyPhraseID, Count(tbmSeedPhrases.SeedPhrase) AS CountOfSeedPhrase FROM (tbmAdGroups LEFT JOIN tbmKeyPhrases ON tbmAdGroups.AdGroupID = tbmKeyPhrases.AdGroupID) RIGHT JOIN tbmSeedPhrases ON tbmAdGroups.AdGroupID = tbmSeedPhrases.AdGroupID WHERE tbmAdGroups.DestinationID=1 GROUP BY tbmAdGroups.AdGroupID, tbmAdGroups.AdGroupName;
Thanks,
Adam