Link to home
Create AccountLog in
Avatar of Justin Heath
Justin HeathFlag 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
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Question abandoned
Provided solution