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
Justin HeathBusiness Intelligence AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try this solution. Your output is incorrect for many cases. Example1.. is
Richard   >     Book33.  -> in second  table we do not have Id that matches -> ...2      Richard      Book33

DATA
CREATE Table table11
(
	 ID  INT    
	,Name   VARCHAR(20) 
)
GO

INSERT INTO table11 VALUES
(1,'Justin'),
(2,'Richard')
GO

CREATE Table table21
(
	 ID  INT    
	,Name   VARCHAR(20)   
	,Books VARCHAR(20)
)
INSERT INTO table21 VALUES
(1      ,'Justin','Book1'),
(2      ,'Richard','Book1'),
(2      ,'Richard','Book10'),
(2      ,'Richard','Book23')
GO

CREATE Table table31
(
	 ID  INT    
	,Name   VARCHAR(20)   
	,Books VARCHAR(20)
)
GO

INSERT INTO table31 VALUES
(1      ,'Justin','Book1'),
(2      ,'Richard','Book1'),
(2      ,'Richard','Book10'),
(2      ,'Richard','Book23'),
(2      ,'Richard','Book33')
GO

Open in new window


SOLUTION
SELECT a.ID,a.Name,a.Books,b.ID,b.Name,b.Books,c.ID,c.Name
FROM 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY ID) rnk FROM table31
)a
FULL JOIN
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY ID) rnk FROM table21
)b ON a.ID = b.ID AND a.rnk = b.rnk
FULL JOIN 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY ID) rnk FROM table11
)c ON C.ID = a.ID AND a.rnk = c.rnk

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
ID          Name                 Books                ID          Name                 Books                ID          Name
----------- -------------------- -------------------- ----------- -------------------- -------------------- ----------- --------------------
1           Justin               Book1                1           Justin               Book1                1           Justin
2           Richard              Book1                2           Richard              Book1                2           Richard
2           Richard              Book10               2           Richard              Book10               NULL        NULL
2           Richard              Book23               2           Richard              Book23               NULL        NULL
2           Richard              Book33               NULL        NULL                 NULL                 NULL        NULL

(5 row(s) affected)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Justin HeathBusiness Intelligence AnalystAuthor Commented:
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.
Snarf0001Commented:
Okay, based on that, you need to full join to tables 2 and 3 first, and then join that result to t1 based on the ID.
Output is virtually the same, except T2_ID and T2_Name should be null as well, since the record for "Richard - Book33" doesn't exist in T2.

select t1.ID as T1_ID, x.T2_ID, x.T3_ID,
	t1.Name as T1_Name, x.T2_Name, x.T3_Name,
	x.T2_Books, x.T3_Books
from Temp1 t1
join (
	select isnull(T2.ID, T3.ID) as JoinID, T2.ID as T2_ID, T3.ID as T3_ID, T2.Name as T2_Name, T3.Name AS T3_Name, T2.Books as T2_Books, T3.Books as T3_Books
	from Temp2 T2
	full join Temp3 T3 on t2.ID = t3.ID and t2.Books = t3.Books
) x on t1.ID = x.JoinID

Open in new window

Justin HeathBusiness Intelligence AnalystAuthor Commented:
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 KumarDatabase ExpertCommented:
Question abandoned
Provided solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.