troubleshooting Question

SQL - Too Many Records/Rows Returned from subquery

Avatar of Justin Heath
Justin HeathFlag for United Kingdom of Great Britain and Northern Ireland asked on
SQL
5 Comments2 Solutions131 ViewsLast Modified:
Hello, I have 3 tables that I was checking the data in them matched through and my query was returning too many rows and I could not see what was wrong with my query.

These are examples tables:
Table 1 (Temp1)
ID      Name
1      Justin
2      Richard

Table 2 (Temp2)
ID      Name      Books
1      Justin      Book1
2      Richard      Book1
2      Richard      Book10
2      Richard      Book23

Table 3 (Temp3)
ID      Name      Books
1      Justin      Book1
2      Richard      Book1
2      Richard      Book10
2      Richard      Book23
2      Richard      Book33

Result
T1_ID      T2_ID      T3_ID      T1_Name      T2_Name      T3_Name      T2_Books      T3_Books
1          1          1          Justin       Justin       Justin       Book1         Book1
2          2          2          Richard      Richard      Richard      Book1         Book1
2          2          2          Richard      Richard      Richard      Book1         Book10
2          2          2          Richard      Richard      Richard      Book1         Book23
2          2          2          Richard      Richard      Richard      Book10        Book1
2          2          2          Richard      Richard      Richard      Book10        Book10
2          2          2          Richard      Richard      Richard      Book10        Book23
2          2          2          Richard      Richard      Richard      Book23        Book1
2          2          2          Richard      Richard      Richard      Book23        Book10
2          2          2          Richard      Richard      Richard      Book23        Book23

The Result I wanted to see
T1_ID      T2_ID      T3_ID      T1_Name      T2_Name      T3_Name      T2_Books      T3_Books
1          1          1          Justin       Justin       Justin       Book1         Book1
2          2          2          Richard      Richard      Richard      Book1         Book1
2          2          2          Richard      Richard      Richard      Book10        Book10
2          2          2          Richard      Richard      Richard      Book23        Book23
2          2          2          Richard      Richard      Richard      NULL          Book33

This is the query I wrote
SELECT      Test.T1_ID,Test.T2_ID,Test.T3_ID,
                  Test.T1_Name,Test.T2_Name,Test.T3_Name,
                  Test.T2_Books,Test.T3_Books                  
  FROM      (
            SELECT T1.ID AS T1_ID,T2.ID AS T2_ID,T3.ID AS T3_ID,
            T1.Name AS T1_Name,T2.Name AS T2_Name,T3.Name AS T3_Name,
            T2.Books AS T2_Books,T3.Books AS T3_Books
            FROM [TestDBJustin].[dbo].[Temp1] AS T1 
            LEFT JOIN [TestDBJustin].[dbo].[Temp2] AS T2 ON T1.ID = T2.ID
            LEFT JOIN [TestDBJustin].[dbo].[Temp3] AS T3 ON T1.ID = T3.ID
            ) AS Test

Hope you can help
Thanks Justin
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros