Home » SQL & PL/SQL » SQL & PL/SQL » sql join (oracle 10g)
sql join [message #327573] Mon, 16 June 2008 23:47 Go to next message
piscean_n
Messages: 36
Registered: December 2007
Member
I'am joining 2 tables.

select * from PRODUCTS where chg_date = '20080401'
and status in(SELECT STATUS_ID FROM
STATUS_XYZ WHERE STATUS_TYPE_ID IN('XYZ'))

IF PRODUCT TABLE HAS 2 ROWS FOR THE ABOVE GIVEN CONDITION THEN



2 DUPLICATE ROWS ARE COMING AFTER JOING THESE 2 TABLES.BUT I WANT ONLY 1 ROW BECAUSE REQUEST_ID IS UNIQUE IN MASTER TABLE.

SELECT UNIQUE * FROM(SELECT distinct ROWNUM rn,ra.request_id,ra.related_request_id,ra.request_source_id,ra.request_type_id,
ra.advice_level_id,ra.module_id,ra.status,ra.delivery_type_id,ra.account_no,ra.user_id,ra.sales_force_id,
ra.bill_period,ra.bill_cycle_end_date ,ra.request_date,ra.request_completion_date ,ra.chg_date,ra.sent_to_bds
FROM MASTERS ra ,PRODUCTS rs WHERE ra.REQUEST_ID = rs.request_id
AND rs.chg_date = '20080401'  and (rs.status in(SELECT STATUS_ID FROM 
STATUS_XYZ WHERE STATUS_TYPE_ID IN('XYZ') )
OR (rs.status = 'E' and ra.request_type_id = 'L') )
)WHERE rn BETWEEN 1 AND 20



Please can anybody help me which type of join should i use.
Re: sql join [message #327578 is a reply to message #327573] Mon, 16 June 2008 23:54 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Check all the common column of both table are made equal join.
otherwise display your both table structure.


kanish
Re: sql join [message #327600 is a reply to message #327573] Tue, 17 June 2008 00:39 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indent the code (See SQL Formatter) and align the columns in result.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Don't post in UPPER case.

Regards
Michel
Previous Topic: SQL Query re-write assistance requested
Next Topic: Tablespace issues
Goto Forum:
  


Current Time: Fri Dec 09 03:55:54 CST 2016

Total time taken to generate the page: 0.08620 seconds