We help IT Professionals succeed at work.

SQL - Too Many Records/Rows Returned from subquery

Justin Heath
Justin Heath asked
on
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
Comment
Watch Question

Database Expert
Awarded 2016
Top Expert 2016
Commented:
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

Justin HeathBusiness Intelligence Analyst

Author

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.
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 Analyst

Author

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 Expert
Awarded 2016
Top Expert 2016

Commented:
Question abandoned
Provided solution