Home » SQL & PL/SQL » SQL & PL/SQL » Outer join problem
Outer join problem [message #197865] Fri, 13 October 2006 00:22 Go to next message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
hi
i have 2 tables. i have to select all the records from table1 and matching records from table2. i am using outer join for this.my problem is i have to include a condtion for table2 also.because of this it is selecting only matching records from both the table.
but i want all the records from table1

this is my query.
----
Select PM.*,TD.QTY_Return as OB from Product_Master as PM LEFT OUTER JOIN Txn_Details TD ON PM.Product_ID=TD.Product_ID where TD.Txn_No=(Select Txn_No from Txn_Hdr where Line_Route='ABC' AND Txn_Date=(Select MAX(Txn_Date) from Txn_Hdr where Line_Route='ABC')) Order by PM.Product_Id

--- value 'ABC' is generated dynamically
------

can anyone help me.

thanks
Re: Outer join problem [message #197875 is a reply to message #197865] Fri, 13 October 2006 01:27 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Couldn't test it, whithout any example create and insert scripts, but I gave it a try:

SELECT pm.*
      ,td.qty_return ob
FROM   product_master pm
      ,txn_details td
      ,txn_hdr th
      ,(SELECT MAX(txn_date) max_date
        FROM   txn_hdr th2
        WHERE  th2.line_route = 'ABC') md
WHERE  pm.product_id = td.product_id(+)
       AND td.txn_no = th.txn_no(+)
       AND td.txn_date = md.max_date(+)
ORDER  BY pm.product_id


I'm sure the same thing can be done using the new join syntax, but since I can't test it, I go for the one I know better...

When you're outer joining you have to "strech" the outerjoin to the following tables to, like:
SELECT 1
FROM   a
      ,b
      ,c
WHERE  a.column1 = b.column2(+)
       AND b.column3 = c.column4(+)


As for the max(date) you need, it's better for performance to create a subquery to find the max(date) once. In you example, you lookup up the max(date) again and again for each result of the query.

Regards,
Sabine
Re: Outer join problem [message #197924 is a reply to message #197875] Fri, 13 October 2006 05:13 Go to previous messageGo to next message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
hi
thanks for reply
the query you have sent is giving the same result as mine.

this is my table structure.

product_master
---------------
Product_Id number (Primary)
product_Name


Txn_Hdr
---------
Txn_No number (Primary)
Txn_Date date
Route_Name varchar


Txn_Details
-----------
Txn_No number (Foreign)
Product_Id number
Qty_Return number
Txn_Details holds details for multiple txn_nos


i want to select all the product from product_master
and matching record from txn_Details

e.g
Product_Name Return_Qty
--------- -------------
Product1 10
Product2 1
Product3 5
Product4 8
Product5 6
Product6
Product7
Product8



Re: Outer join problem [message #197984 is a reply to message #197924] Fri, 13 October 2006 11:05 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What is the PK for txn_details?
And in your example resultset, it looks as if you're either summing up the returned quantity, or every product has been sold & returned only once, and that doesn't make sense to me. What should it be?

[Updated on: Fri, 13 October 2006 11:12]

Report message to a moderator

Re: Outer join problem [message #198034 is a reply to message #197984] Fri, 13 October 2006 23:07 Go to previous messageGo to next message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
skooman wrote on Fri, 13 October 2006 21:35

What is the PK for txn_details?
And in your example resultset, it looks as if you're either summing up the returned quantity, or every product has been sold & returned only once, and that doesn't make sense to me. What should it be?



the structure i have given does not contain all the fields

pk for txn_details is id(number)

i dont want to sum the returned qty. i want every product.the query is simple if i am not including condition for txn_details. using left outer join it will select all the records from product_master table. but this will get products for all the txn_nos

some sample records
Txn_Hdr

Txn_No  Txn_Date      Route
----------------------------------
1        14-Oct-2006   Route_1
2        14-Oct-2006   Route_2

Txn_Details

Id   Txn_No   Product_Id   Sold_Qty    Return_Qty   Damage
-----------------------------------------------------------
1     1           1         10            2           2
2     1           2         5             1           0  
3     1           3         20            0           0
4     1           4         5             3           0     
5     2           1         50            0           1
6     2           2         23            0           3
7     2           3         40            2           0   
8     2           4         70            6           0 
9     2           5         90            0           0 
10    2           6        100            0           2


Product_Tabale

Product_Id   Product_Name
----------------------------------------------------------
1             Product_1
2             Product_2
3             Product_3
4             Product_4
5             Product_5
6             Product_6
7             Product_7
8             Product_8
9             Product_9
10            Product_10

Result i am getting is

Product_Id    Product_Name   Txn_No   Return_Qty
------------------------------------------------
1             Product_1         1    2
2             Product_2         1    1
3             Product_3         1    0 
4             Product_4         1    3


Result what i want is
Product_Id    Product_Name   Txn_No   Return_Qty
------------------------------------------------
1             Product_1         1    2
2             Product_2         1    1
3             Product_3         1    0 
4             Product_4         1    3
5             Product_5         
6             Product_6         
7             Product_7    
8             Product_8    
9             Product_9    
10            Product_10   


[Updated on: Fri, 13 October 2006 23:10]

Report message to a moderator

Re: Outer join problem [message #198129 is a reply to message #198034] Sun, 15 October 2006 11:49 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> ed
Wrote file afiedt.buf

  1  select distinct a.projectid, a.product_name , b.txn_no, b.return_qty
  2  from product_table a , txn_details b
  3  where a.projectid = b.productid(+)
  4* order by a.projectid
SQL> /

 PROJECTID PRODUCT_NAME             TXN_NO RETURN_QTY
---------- -------------------- ---------- ----------
         1 Product_1                     1          2
         1 Product_1                     2          0
         2 Product_2                     1          1
         2 Product_2                     2          0
         3 Product_3                     1          0
         3 Product_3                     2          2
         4 Product_4                     1          3
         4 Product_4                     2          6
         5  Product_5                    2          0
         6  Product_6                    2          0
         7  Product_7

 PROJECTID PRODUCT_NAME             TXN_NO RETURN_QTY
---------- -------------------- ---------- ----------
         8 Product_8
         9 Product_9
        10 Product_10

14 rows selected.

SQL>


plz. let me know if above query help you. thanx
Re: Outer join problem [message #198249 is a reply to message #198129] Mon, 16 October 2006 05:01 Go to previous messageGo to next message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
user52 wrote on Sun, 15 October 2006 22:19
  1  select distinct a.projectid, a.product_name , b.txn_no, b.return_qty
  2  from product_table a , txn_details b
  3  where a.projectid = b.productid(+)
  4* order by a.projectid


plz. let me know if above query help you. thanx



this query gives me the same result as mine. my problem is i want to include the condition for txn_no. i,e where b.Txn_no=1.
when i include this condition i get only those record which are there in txn_detail table.but i want all the records from product table.

regards

Re: Outer join problem [message #199034 is a reply to message #198249] Thu, 19 October 2006 16:20 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Ok, now I see what you want. Create a subquery for all returned items for this route_1, then outerjoin that complete subquery with the product_table:

SQL> SELECT pt.product_id
  2  ,      pt.product_name
  3  ,      rt.sum_returned
  4  FROM   product_table pt
  5        ,(SELECT td.product_id
  6                ,SUM(td.return_qty) sum_returned
  7          FROM   txn_hdr     th
  8                ,txn_details td
  9          WHERE  td.txn_no = th.txn_no
 10                 AND th.route = 'route_1'
 11          GROUP  BY td.product_id) rt
 12  WHERE  pt.product_id = rt.product_id(+)
 13  /

PRODUCT_ID PRODUCT_NAME    SUM_RETURNED
---------- --------------- ------------
         1 Product_1                  2
         2 Product_2                  1
         3 Product_3                  0
         4 Product_4                  3
         5 Product_5       
         6 Product_6       
         7 Product_7       
         8 Product_8       
         9 Product_9       
        10 Product_10      

10 rows selected


Regards,
Sabine
Re: Outer join problem [message #199418 is a reply to message #197865] Tue, 24 October 2006 03:14 Go to previous message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
thanks skooman. this is the result i was trying to get.
Previous Topic: Unique Keys per table
Next Topic: ORA-06502: PL/SQL: numeric or value error (merged)
Goto Forum:
  


Current Time: Sun Dec 11 06:05:47 CST 2016

Total time taken to generate the page: 0.07075 seconds