koossa
asked on
SQL Server Enterprise manager changing my query.
Good day
I have the following query that works perfectly in ASP and give me results:
SELECT COUNT(Tree.ChildUserID) AS Level1, Users_1.[Transaction Status] AS Status1, COUNT(Tree_1.ChildUserID) AS Level2, Users_2.[Transaction Status] AS Status2, COUNT(Tree_2.ChildUserID) AS Level3, Users_3.[Transaction Status] AS Status3, COUNT(Tree_3.ChildUserID) AS Level4, Users_4.[Transaction Status] AS Status4, COUNT(Tree_4.ChildUserID) AS Level5, Users_5.[Transaction Status] AS Status5, COUNT(Tree_5.ChildUserID) AS Level6, Users_6.[Transaction Status] AS Status6, COUNT(Tree_6.ChildUserID) AS Level7, Users_7.[Transaction Status] AS Status7 FROM Tree Tree_6 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_7 ON Tree_6.ChildUserID = ProductSubscriptions_7.Use rID LEFT OUTER JOIN Users Users_7 ON Tree_6.ChildUserID = Users_7.ID RIGHT OUTER JOIN Users Users_6 ON Tree_6.ParentUserID = Users_6.ID RIGHT OUTER JOIN Tree Tree_5 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_6 ON Tree_5.ChildUserID = ProductSubscriptions_6.Use rID ON Users_6.ID = Tree_5.ChildUserID RIGHT OUTER JOIN Users Users_5 ON Tree_5.ParentUserID = Users_5.ID RIGHT OUTER JOIN Tree Tree_4 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_5 ON Tree_4.ChildUserID = ProductSubscriptions_5.Use rID ON Users_5.ID = Tree_4.ChildUserID RIGHT OUTER JOIN Users Users_4 ON Tree_4.ParentUserID = Users_4.ID RIGHT OUTER JOIN Tree Tree_3 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_4 ON Tree_3.ChildUserID = ProductSubscriptions_4.Use rID ON Users_4.ID = Tree_3.ChildUserID RIGHT OUTER JOIN Users Users_3 ON Tree_3.ParentUserID = Users_3.ID RIGHT OUTER JOIN Tree Tree_2 RIGHT OUTER JOIN Users Users_2 ON Tree_2.ParentUserID = Users_2.ID LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_3 ON Tree_2.ChildUserID = ProductSubscriptions_3.Use rID ON Users_3.ID = Tree_2.ChildUserID RIGHT OUTER JOIN Tree Tree_1 RIGHT OUTER JOIN Users Users_1 ON Tree_1.ParentUserID = Users_1.ID LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_2 ON Tree_1.ChildUserID = ProductSubscriptions_2.Use rID ON Users_2.ID = Tree_1.ChildUserID RIGHT OUTER JOIN ProductSubscriptions INNER JOIN Users ON ProductSubscriptions.UserI D = Users.ID INNER JOIN Tree ON Users.ID = Tree.ParentUserID LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_1 ON Tree.ChildUserID = ProductSubscriptions_1.Use rID ON Users_1.ID = Tree.ChildUserID WHERE (ProductSubscriptions.Prod uctID = 1) AND (Users.Active = 1) AND (Users.ID = 47) AND (ProductSubscriptions_1.Pr oductID = 1 OR ProductSubscriptions_1.Pro ductID IS NULL) AND (Users_1.Active = 1) AND (ProductSubscriptions_2.Pr oductID = 1 OR ProductSubscriptions_2.Pro ductID IS NULL) AND (Users_2.Active = 1 OR Users_2.Active IS NULL) AND (ProductSubscriptions_3.Pr oductID = 1 OR ProductSubscriptions_3.Pro ductID IS NULL) AND (Users_3.Active = 1 OR Users_3.Active IS NULL) AND (ProductSubscriptions_4.Pr oductID = 1 OR ProductSubscriptions_4.Pro ductID IS NULL) AND (Users_4.Active = 1 OR Users_4.Active IS NULL) AND (ProductSubscriptions_5.Pr oductID = 1 OR ProductSubscriptions_5.Pro ductID IS NULL) AND (Users_5.Active = 1 OR Users_5.Active IS NULL) AND (ProductSubscriptions_6.Pr oductID = 1 OR ProductSubscriptions_6.Pro ductID IS NULL) AND (Users_6.Active = 1 OR Users_6.Active IS NULL) AND (ProductSubscriptions_7.Pr oductID = 1 OR ProductSubscriptions_7.Pro ductID IS NULL) AND (Users_7.Active = 1 OR Users_7.Active IS NULL) GROUP BY Users_1.[Transaction Status], Users_2.[Transaction Status], Users_3.[Transaction Status], Users_4.[Transaction Status], Users_5.[Transaction Status], Users_6.[Transaction Status], Users_7.[Transaction Status] HAVING (Users_1.[Transaction Status] <= 1 OR Users_1.[Transaction Status] IS NULL) AND (Users_2.[Transaction Status] <= 1 OR Users_2.[Transaction Status] IS NULL) AND (Users_3.[Transaction Status] <= 1 OR Users_3.[Transaction Status] IS NULL) AND (Users_4.[Transaction Status] <= 1 OR Users_4.[Transaction Status] IS NULL) AND (Users_5.[Transaction Status] <= 1 OR Users_5.[Transaction Status] IS NULL) AND (Users_6.[Transaction Status] <= 1 OR Users_6.[Transaction Status] IS NULL) AND (Users_7.[Transaction Status] <= 1 OR Users_7.[Transaction Status] IS NULL);
But when I copy and paste it into SQL Enterprise manager it changed something in the query and there are suddenly no records to return
This is the changed query:
SELECT Â Â COUNT(Tree_6.ChildUserID) AS Level1, Users_1.[Transaction Status] AS Status1, COUNT(Tree_1.ChildUserID) AS Level2,
           Users_2.[Transaction Status] AS Status2, COUNT(Tree_2.ChildUserID) AS Level3, Users_3.[Transaction Status] AS Status3, COUNT(Tree_3.ChildUserID)
            AS Level4, Users_4.[Transaction Status] AS Status4, COUNT(Tree_4.ChildUserID) AS Level5, Users_5.[Transaction Status] AS Status5,
           COUNT(Tree_5.ChildUserID) AS Level6, Users_6.[Transaction Status] AS Status6, COUNT(Tree_6.ChildUserID) AS Level7,
           Users_7.[Transaction Status] AS Status7
FROM Â Â Â Â Tree Tree_6 LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_7 ON Tree_6.ChildUserID = ProductSubscriptions_7.Use rID LEFT OUTER JOIN
           Users Users_7 ON Tree_6.ChildUserID = Users_7.ID RIGHT OUTER JOIN
           Users Users_6 ON Tree_6.ParentUserID = Users_6.ID RIGHT OUTER JOIN
           Tree Tree_5 LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_6 ON Tree_5.ChildUserID = ProductSubscriptions_6.Use rID ON
           Users_6.ID = Tree_5.ChildUserID RIGHT OUTER JOIN
           Users Users_5 ON Tree_5.ParentUserID = Users_5.ID RIGHT OUTER JOIN
           Tree Tree_4 LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_5 ON Tree_4.ChildUserID = ProductSubscriptions_5.Use rID ON
           Users_5.ID = Tree_4.ChildUserID RIGHT OUTER JOIN
           Users Users_4 ON Tree_4.ParentUserID = Users_4.ID RIGHT OUTER JOIN
           Tree Tree_3 LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_4 ON Tree_3.ChildUserID = ProductSubscriptions_4.Use rID ON
           Users_4.ID = Tree_3.ChildUserID RIGHT OUTER JOIN
           Users Users_3 ON Tree_3.ParentUserID = Users_3.ID RIGHT OUTER JOIN
           Tree Tree_2 RIGHT OUTER JOIN
           Users Users_2 ON Tree_2.ParentUserID = Users_2.ID LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_3 ON Tree_2.ChildUserID = ProductSubscriptions_3.Use rID ON
           Users_3.ID = Tree_2.ChildUserID RIGHT OUTER JOIN
           Tree Tree_1 RIGHT OUTER JOIN
           Users Users_1 ON Tree_1.ParentUserID = Users_1.ID LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_2 ON Tree_1.ChildUserID = ProductSubscriptions_2.Use rID ON
           Users_2.ID = Tree_1.ChildUserID RIGHT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_8 INNER JOIN
           Users Users_8 ON ProductSubscriptions_8.Use rID = Users_8.ID INNER JOIN
           Tree Tree_7 ON Users_8.ID = Tree_7.ParentUserID LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_1 ON Tree_7.ChildUserID = ProductSubscriptions_1.Use rID ON Users_1.ID = Tree_7.ChildUserID
WHERE Â Â (ProductSubscriptions_7.Pr oductID = 1) AND (Users_7.Active = 1) AND (Users_7.ID = 47) AND (ProductSubscriptions_1.Pr oductID = 1 OR
           ProductSubscriptions_1.Pro ductID IS NULL) AND (Users_1.Active = 1) AND (ProductSubscriptions_2.Pr oductID = 1 OR
           ProductSubscriptions_2.Pro ductID IS NULL) AND (Users_2.Active = 1 OR
           Users_2.Active IS NULL) AND (ProductSubscriptions_3.Pr oductID = 1 OR
           ProductSubscriptions_3.Pro ductID IS NULL) AND (Users_3.Active = 1 OR
           Users_3.Active IS NULL) AND (ProductSubscriptions_4.Pr oductID = 1 OR
           ProductSubscriptions_4.Pro ductID IS NULL) AND (Users_4.Active = 1 OR
           Users_4.Active IS NULL) AND (ProductSubscriptions_5.Pr oductID = 1 OR
           ProductSubscriptions_5.Pro ductID IS NULL) AND (Users_5.Active = 1 OR
           Users_5.Active IS NULL) AND (ProductSubscriptions_6.Pr oductID = 1 OR
           ProductSubscriptions_6.Pro ductID IS NULL) AND (Users_6.Active = 1 OR
           Users_6.Active IS NULL) AND (ProductSubscriptions_7.Pr oductID = 1 OR
           ProductSubscriptions_7.Pro ductID IS NULL) AND (Users_7.Active = 1 OR
           Users_7.Active IS NULL)
GROUP BY Users_1.[Transaction Status], Users_2.[Transaction Status], Users_3.[Transaction Status], Users_4.[Transaction Status],
           Users_5.[Transaction Status], Users_6.[Transaction Status], Users_7.[Transaction Status]
HAVING Â Â Â (Users_1.[Transaction Status] <= 1 OR
           Users_1.[Transaction Status] IS NULL) AND (Users_2.[Transaction Status] <= 1 OR
           Users_2.[Transaction Status] IS NULL) AND (Users_3.[Transaction Status] <= 1 OR
           Users_3.[Transaction Status] IS NULL) AND (Users_4.[Transaction Status] <= 1 OR
           Users_4.[Transaction Status] IS NULL) AND (Users_5.[Transaction Status] <= 1 OR
           Users_5.[Transaction Status] IS NULL) AND (Users_6.[Transaction Status] <= 1 OR
           Users_6.[Transaction Status] IS NULL) AND (Users_7.[Transaction Status] <= 1 OR
           Users_7.[Transaction Status] IS NULL)
Any idea why this is happening?
I have the following query that works perfectly in ASP and give me results:
SELECT COUNT(Tree.ChildUserID) AS Level1, Users_1.[Transaction Status] AS Status1, COUNT(Tree_1.ChildUserID) AS Level2, Users_2.[Transaction Status] AS Status2, COUNT(Tree_2.ChildUserID) AS Level3, Users_3.[Transaction Status] AS Status3, COUNT(Tree_3.ChildUserID) AS Level4, Users_4.[Transaction Status] AS Status4, COUNT(Tree_4.ChildUserID) AS Level5, Users_5.[Transaction Status] AS Status5, COUNT(Tree_5.ChildUserID) AS Level6, Users_6.[Transaction Status] AS Status6, COUNT(Tree_6.ChildUserID) AS Level7, Users_7.[Transaction Status] AS Status7 FROM Tree Tree_6 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_7 ON Tree_6.ChildUserID = ProductSubscriptions_7.Use
But when I copy and paste it into SQL Enterprise manager it changed something in the query and there are suddenly no records to return
This is the changed query:
SELECT Â Â COUNT(Tree_6.ChildUserID) AS Level1, Users_1.[Transaction Status] AS Status1, COUNT(Tree_1.ChildUserID) AS Level2,
           Users_2.[Transaction Status] AS Status2, COUNT(Tree_2.ChildUserID) AS Level3, Users_3.[Transaction Status] AS Status3, COUNT(Tree_3.ChildUserID)
            AS Level4, Users_4.[Transaction Status] AS Status4, COUNT(Tree_4.ChildUserID) AS Level5, Users_5.[Transaction Status] AS Status5,
           COUNT(Tree_5.ChildUserID) AS Level6, Users_6.[Transaction Status] AS Status6, COUNT(Tree_6.ChildUserID) AS Level7,
           Users_7.[Transaction Status] AS Status7
FROM Â Â Â Â Tree Tree_6 LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_7 ON Tree_6.ChildUserID = ProductSubscriptions_7.Use
           Users Users_7 ON Tree_6.ChildUserID = Users_7.ID RIGHT OUTER JOIN
           Users Users_6 ON Tree_6.ParentUserID = Users_6.ID RIGHT OUTER JOIN
           Tree Tree_5 LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_6 ON Tree_5.ChildUserID = ProductSubscriptions_6.Use
           Users_6.ID = Tree_5.ChildUserID RIGHT OUTER JOIN
           Users Users_5 ON Tree_5.ParentUserID = Users_5.ID RIGHT OUTER JOIN
           Tree Tree_4 LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_5 ON Tree_4.ChildUserID = ProductSubscriptions_5.Use
           Users_5.ID = Tree_4.ChildUserID RIGHT OUTER JOIN
           Users Users_4 ON Tree_4.ParentUserID = Users_4.ID RIGHT OUTER JOIN
           Tree Tree_3 LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_4 ON Tree_3.ChildUserID = ProductSubscriptions_4.Use
           Users_4.ID = Tree_3.ChildUserID RIGHT OUTER JOIN
           Users Users_3 ON Tree_3.ParentUserID = Users_3.ID RIGHT OUTER JOIN
           Tree Tree_2 RIGHT OUTER JOIN
           Users Users_2 ON Tree_2.ParentUserID = Users_2.ID LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_3 ON Tree_2.ChildUserID = ProductSubscriptions_3.Use
           Users_3.ID = Tree_2.ChildUserID RIGHT OUTER JOIN
           Tree Tree_1 RIGHT OUTER JOIN
           Users Users_1 ON Tree_1.ParentUserID = Users_1.ID LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_2 ON Tree_1.ChildUserID = ProductSubscriptions_2.Use
           Users_2.ID = Tree_1.ChildUserID RIGHT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_8 INNER JOIN
           Users Users_8 ON ProductSubscriptions_8.Use
           Tree Tree_7 ON Users_8.ID = Tree_7.ParentUserID LEFT OUTER JOIN
           ProductSubscriptions ProductSubscriptions_1 ON Tree_7.ChildUserID = ProductSubscriptions_1.Use
WHERE Â Â (ProductSubscriptions_7.Pr
           ProductSubscriptions_1.Pro
           ProductSubscriptions_2.Pro
           Users_2.Active IS NULL) AND (ProductSubscriptions_3.Pr
           ProductSubscriptions_3.Pro
           Users_3.Active IS NULL) AND (ProductSubscriptions_4.Pr
           ProductSubscriptions_4.Pro
           Users_4.Active IS NULL) AND (ProductSubscriptions_5.Pr
           ProductSubscriptions_5.Pro
           Users_5.Active IS NULL) AND (ProductSubscriptions_6.Pr
           ProductSubscriptions_6.Pro
           Users_6.Active IS NULL) AND (ProductSubscriptions_7.Pr
           ProductSubscriptions_7.Pro
           Users_7.Active IS NULL)
GROUP BY Users_1.[Transaction Status], Users_2.[Transaction Status], Users_3.[Transaction Status], Users_4.[Transaction Status],
           Users_5.[Transaction Status], Users_6.[Transaction Status], Users_7.[Transaction Status]
HAVING Â Â Â (Users_1.[Transaction Status] <= 1 OR
           Users_1.[Transaction Status] IS NULL) AND (Users_2.[Transaction Status] <= 1 OR
           Users_2.[Transaction Status] IS NULL) AND (Users_3.[Transaction Status] <= 1 OR
           Users_3.[Transaction Status] IS NULL) AND (Users_4.[Transaction Status] <= 1 OR
           Users_4.[Transaction Status] IS NULL) AND (Users_5.[Transaction Status] <= 1 OR
           Users_5.[Transaction Status] IS NULL) AND (Users_6.[Transaction Status] <= 1 OR
           Users_6.[Transaction Status] IS NULL) AND (Users_7.[Transaction Status] <= 1 OR
           Users_7.[Transaction Status] IS NULL)
Any idea why this is happening?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
well, EM may add some additional information in your T-SQL but not add something which make your code erroneous. You have to make sure that your query is working fine in Query Analyzer and not working in EM. than let us know both the query.
Please run this query in Query analyser. EM might change the Query slightly....
ASKER
Yes, the top one is working in QA, but when I copy it to EM it change something.
When I copied the changed code to QA, it does not work either.
When I copied the changed code to QA, it does not work either.
can you post both version (EM and QA) of query here?
they are in question RiteshShah
ASKER
The query at the top is the QA one and the 2nd one is the EM one.
I have figure out what the problem is:
I have used some of the tables more than once where a underscore and number is put after the table. (table_1)
But my original table did not have an alias.
After changing the original table to table_0 it's working.
Thank you
I have figure out what the problem is:
I have used some of the tables more than once where a underscore and number is put after the table. (table_1)
But my original table did not have an alias.
After changing the original table to table_0 it's working.
Thank you
If you will observe part of your query
RIGHT OUTER JOIN Tree Tree_5
LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_6
ON Tree_5.ChildUserID = ProductSubscriptions_6.Use rID
ON Users_6.ID = Tree_5.ChildUserID
there is no ON between RIght OUTER and Left OUTER join, instead, both ON comes after Right and Left outer. this kind of junk is happening which is haulting your query from run.
RIGHT OUTER JOIN Tree Tree_5
LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_6
ON Tree_5.ChildUserID = ProductSubscriptions_6.Use
ON Users_6.ID = Tree_5.ChildUserID
there is no ON between RIght OUTER and Left OUTER join, instead, both ON comes after Right and Left outer. this kind of junk is happening which is haulting your query from run.
As far as I know EM may add junk to your queries.
Personally i try to only use QA
it article may help http://databases.aspfaq.com/database/which-tool-should-i-use-enterprise-manager-or-query-analyzer.html