- Oracle PL/SQL
- Oracle Database
- Query Syntax
- SQL
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)
Table 2 (Temp2)
Table 3 (Temp3)
Result
The Result I wanted to see
This is the query I wrote
Hope you can help
Thanks Justin
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
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
SOLUTION
OUTPUT
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
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
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)
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.
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial