Merge two Queries (Sub Query)? [message #329118] |
Tue, 24 June 2008 03:23  |
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 #329147 is a reply to message #329118] |
Tue, 24 June 2008 04:50   |
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 #329158 is a reply to message #329118] |
Tue, 24 June 2008 05:48   |
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  |
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
|
|
|