Avatar of Justin Heath
Justin Heath
Flag for United Kingdom of Great Britain and Northern Ireland asked on

SQL - Too Many Records/Rows Returned from subquery

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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


Hope you can help
Thanks Justin
SQL

Avatar of undefined
Last Comment
Pawan Kumar

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Pawan Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Justin Heath

ASKER
I think I have confused things by using 'ID' as a column header.  ID = UserID therefore 'book33' has an ID of '2' because it is one of richards books.  So Table1 is like a user table, table 2 is a record of there books as is table 3.  But I am wanting to check that table 2 & 3 match, so giving one row for each book that matches per user and I would have one result that does not match to table2 i.e. book33.
SOLUTION
Snarf0001

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Justin Heath

ASKER
Thanks sen5an, Pawan and snarf0001 for your responses I will try your suggestions and see what result I get.  I think it is something to do with how I have joined the tables but just was not certain.
Pawan Kumar

Question abandoned
Provided solution
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck