Join Query for fruits & Vegetables

Question

Hi there

I have two tables table 1, table 2 as inputs. ( highlighted in green as shown below)

and would need the output as shown below ( highlighted in yellow) probably after joins one in detailed vie and summary view based on description.

Answer 1

I'm not entirely sure what you want, but you could probably do
-- Detail query
;With cteF As
(Select OrderID, Fruits_description, Fruits, Row_Number() Over(Partition By OrderID Order By Fruits_description) As rn
From Table1),
cteV As
(Select OrderID, Vegetables_description, Vegetables, Row_Number() Over(Partition By OrderID Order By Vegetables_description) As rn
From Table2),
cteT As
(Select OrderID, Toys_description, Toys, Row_Number() Over(Partition By OrderID Order By Toys_description) As rn
From Table3)
Select Coalesce(f.OrderID, v.OrderID, t.OrderID) As OrderID,
f.Fruits_description,
v.Vegetables_description,
t.Toys_description,
IsNull(f.Fruits, 0) As Fruits,
IsNull(v.Vegetables, 0) As Vegetables,
IsNull(t.Toys, 0) As Toys
From cteF f
Full Outer Join cteV v On f.OrderID = v.OrderID And f.rn = v.rn
Full Outer Join cteT t On IsNull(f.OrderID, v.OrderID) = t.OrderID And IsNull(f.rn, v.rn) = t.rn
Order By OrderID;

You said you use PL/SQL. That is Oracle. This is a SQL Server forum. You want to ask Oracle questions in an Oracle forum. The two products are different and in many cases the right answer for SQL Server will not be the answer you need in Oracle.

Tom

Answer 2

Create Table Table1(OrderID varchar(10), Fruits_description varchar(30), Fruits int);
Insert Table1(OrderID, Fruits_description, Fruits) Values
('Order1', 'Mango', 1),
('Order1', 'Water Melon', 1),
('Order2', 'Mango', 1);
Create Table Table2(OrderID varchar(10), Vegetables_description varchar(30), Vegetables int);
Insert Table2(OrderID, Vegetables_description, Vegetables) Values
('Order1', 'Brinjal', 1),
('Order2', 'Onion', 1),
('Order2', 'Cauliflower', 1);

-- Detail query
;With cteF As
(Select OrderID, Fruits_description, Fruits, Row_Number() Over(Partition By OrderID Order By Fruits_description) As rn
From Table1),
cteV As
(Select OrderID, Vegetables_description, Vegetables, Row_Number() Over(Partition By OrderID Order By Vegetables_description) As rn
From Table2)
Select IsNull(f.OrderID, v.OrderID) As OrderID,
  f.Fruits_description, 
  v.Vegetables_description, 
  IsNull(f.Fruits, 0) As Fruits,
  IsNull(v.Vegetables, 0) As Vegetables
From cteF f
Full Outer Join cteV v On f.OrderID = v.OrderID And f.rn = v.rn
Order By OrderID;

-- Summary query
;With cte As
(
Select Fruits_description, Fruits As Total
From Table1
Union All
Select Vegetables_description As Fruits_description, Vegetables As Total
From Table2)
Select Fruits_description, Sum(Total) As Total
From cte
Group By Fruits_description
Order By Total Desc;

Tom

P.S. Please do not give input as images. Give the input as Create Table and Insert statements (like I did above). Showing us the desired result as an image is fine.

Answer 3

Hi @Fareed Shaik ，

Join table3:

     ;With cteF As  
     (Select OrderID, Fruits_description, Fruits, Row_Number() Over(Partition By OrderID Order By Fruits_description) As rn  
     From Table1),  
     cteV As  
     (Select OrderID, Vegetables_description, Vegetables, Row_Number() Over(Partition By OrderID Order By Vegetables_description) As rn  
     From Table2)  
     , cte3 As  
     (Select OrderID, Toys_description, Toys, Row_Number() Over(Partition By OrderID Order By Toys_description) As rn  
     From Table3)  
      
     Select IsNull(f.OrderID, v.OrderID) As OrderID,  
       f.Fruits_description,   
       v.Vegetables_description,  
       c3.Toys_description,  
       IsNull(f.Fruits, 0) As Fruits,  
       IsNull(v.Vegetables, 0) As Vegetables  
       IsNull(c3.Toys, 0) As Toys  
     From cteF f  
     Full Outer Join cteV v On f.OrderID = v.OrderID And f.rn = v.rn  
     Full Outer Join cte3 c3 on v.OrderID=c3.OrderID and v.rn=c3.rn  
     Order By OrderID;  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi Tom

Thank you so much!!!!!

Full Outer Join cteT t On IsNull(f.OrderID, v.OrderID) = t.OrderID And IsNull(f.rn, v.rn) = t.rn

This join has helped me acheiving my requirement.