Home » SQL & PL/SQL » SQL & PL/SQL » Merge two Queries (Sub Query)? (Oracle 10g)
Merge two Queries (Sub Query)? [message #329118] Tue, 24 June 2008 03:23 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi & Good Morning,

I have the following two queries: -

select      tr.id, tr.trans_num, tr.pricelevel_id, ar.code, ar.description,
            ta.price, ta.pricelevel_id, pr.uom_unit_id,
            pr.price std_price
from        transactions tr, trans_articles ta, articles ar, prices pr, uom_units um
where       tr.id = ta.transaction_id
and         tr.pricelevel_id <> ta.pricelevel_id
and         ta.article_id = ar.id
and         ta.uom_id = um.id
and         ta.article_id = pr.article_id
and         um.id = pr.uom_unit_id
and         pr.pricelevel_id = '1'
and         ta.price <> pr.price
and         tr.shop_id = '7'
order by    tr.trans_num desc


select      tr.id, tr.trans_num, tr.pricelevel_id, ar.code, ar.description,
            ta.price, ta.pricelevel_id, pr.uom_unit_id,
            pr.price std_price
from        transactions tr, trans_articles ta, articles ar, prices pr
where       tr.id = ta.transaction_id
and         tr.pricelevel_id <> ta.pricelevel_id
and         ta.article_id = ar.id
and         ta.article_id = pr.article_id
and         pr.uom_unit_id is null
and         pr.pricelevel_id = '1'
and         ta.price <> pr.price
and         tr.shop_id = '7'
order by    tr.trans_num desc


My question is how would I put these into one query?

Regards

Re: Merge two Queries (Sub Query)? [message #329125 is a reply to message #329118] Tue, 24 June 2008 03:32 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

See the Union operator

kanish
icon14.gif  Re: Merge two Queries (Sub Query)? [message #329127 is a reply to message #329118] Tue, 24 June 2008 03:43 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

As simple as that! Surprised

Many Thanks
Re: Merge two Queries (Sub Query)? [message #329129 is a reply to message #329118] Tue, 24 June 2008 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have also a look at outer join.

Regards
Michel
Re: Merge two Queries (Sub Query)? [message #329133 is a reply to message #329118] Tue, 24 June 2008 04:05 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Quote:
Have also a look at outer join.

Regards
Michel



I will, but is there any advantage over an outer join?

Regards
Re: Merge two Queries (Sub Query)? [message #329137 is a reply to message #329133] Tue, 24 June 2008 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It all depends on your data and schema model.

Regards
Michel
Re: Merge two Queries (Sub Query)? [message #329147 is a reply to message #329118] Tue, 24 June 2008 04:50 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

I originally started with outer joins, but I couldn't get the results I needed, not all the data was being returned.

I've just to SUM some of the fields in my query and the results are more than if they are not summed?

I presume you can SUM in Union queries?

Regards
Re: Merge two Queries (Sub Query)? [message #329151 is a reply to message #329147] Tue, 24 June 2008 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I presume you can SUM in Union queries?

Yes you can.

Regards
Michel
Re: Merge two Queries (Sub Query)? [message #329158 is a reply to message #329118] Tue, 24 June 2008 05:48 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
I originally started with outer joins, but I couldn't get the results I needed, not all the data was being returned.



post the outer join script.
also with which data was not being returned?

regards,
kaushal mishra
Re: Merge two Queries (Sub Query)? [message #329159 is a reply to message #329158] Tue, 24 June 2008 05:59 Go to previous message
Penfold
Messages: 112
Registered: June 2005
Senior Member
mshrkshl wrote on Tue, 24 June 2008 10:48
Quote:
I originally started with outer joins, but I couldn't get the results I needed, not all the data was being returned.



post the outer join script.
also with which data was not being returned?

regards,
kaushal mishra


Hi,

I'd gladly post the script, but I think without the data it will make it a lot harder to see where the problem is, and the problem is the data is over 5 tables.

A copy of the database along with an over view of what I need to report on might be better and quicker?

Regards
Previous Topic: SQL Query (n th Salary)
Next Topic: Last Full 52 Weeks
Goto Forum:
  


Current Time: Fri Dec 09 07:39:29 CST 2016

Total time taken to generate the page: 0.07486 seconds