Home » SQL & PL/SQL » SQL & PL/SQL » how to combine two queries
how to combine two queries [message #379334] Tue, 06 January 2009 02:07 Go to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi,
i have two queries like below

Query 1:
=======
select emp_id from emp_form  where 
                (TO_CHAR(expected_emp_commission_d,'DD-MM-YYYY'))= TO_CHAR(sysdate+14,'DD-MM-YYYY')
    
Query2:
=======            
   select emp_id from emp_purchase_details where emp_form_raised='N';


I tried by using below query. I am getting the records but is it right way to do ?


select * from paf_form a, paf_purchase_details b where  
                   (TO_CHAR(a.expected_first_commission_d,'DD-MM-YYYY'))= TO_CHAR(sysdate+14,'DD-MM-YYYY') and
				   b.aia_form_raised='N' and a.paf_id = b.paf_id;
 



do any one tell me better way to do this..

Thankq


Re: how to combine two queries [message #379341 is a reply to message #379334] Tue, 06 January 2009 02:34 Go to previous messageGo to next message
mailtonagaraja
Messages: 9
Registered: June 2007
Junior Member
1 solution is you can give aliases to these 2 queries and combine ....

like
select * from
(select * from temp1_emp where .............) a,
(select * from temp2_emp where ...........) b
where a.emp_id=b.emp_id
Re: how to combine two queries [message #379343 is a reply to message #379334] Tue, 06 January 2009 02:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It depends. Do you want to only see emp_id's that fulfill both premises, or do you want to combine the two resultsets?
In the first case use a join (like you did); in the second case use a union.

[Edit: rewrite your logic to compare the two dates.
(TO_CHAR(a.expected_first_commission_d,'DD-MM-YYYY'))= TO_CHAR(sysdate+14,'DD-MM-YYYY')

equals

trunc(a.expected_first_commission_d) = trunc(sysdate + 14)

If a.expected_first_commission_d does not contain any times (i.e. all records have a time of 00:00:00), you can even remove the first trunc, thereby enabling any index-usages, if there is an index on that column.

[Updated on: Tue, 06 January 2009 02:39]

Report message to a moderator

Re: how to combine two queries [message #379354 is a reply to message #379343] Tue, 06 January 2009 03:14 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
ThankQ all for reply,

I don't what union i wwant to only see emp_id's that fulfill both premises.


Re: how to combine two queries [message #379627 is a reply to message #379354] Wed, 07 January 2009 07:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could try this:
   select emp_id 
   from   emp_form
   where  (TO_CHAR(expected_emp_commission_d,'DD-MM-YYYY'))= TO_CHAR(sysdate+14,'DD-MM-YYYY')
intersect
   select emp_id 
   from   emp_purchase_details
   where  emp_form_raised='N';


[tidy up query]

[Updated on: Wed, 07 January 2009 07:01]

Report message to a moderator

Previous Topic: Can not connect to database
Next Topic: pl/sql select loop
Goto Forum:
  


Current Time: Sun Dec 04 20:28:02 CST 2016

Total time taken to generate the page: 0.21563 seconds