PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
SELECT sum(orders) AS value, start_date FROM (
SELECT (
SELECT count(*) from (
SELECT id FROM transactions WHERE effort=efforts.id::text AND tx_type='N'
UNION ALL
SELECT id FROM subscribers_transactions WHERE effort=efforts.id::text AND tx_type='N'
) AS foo
) AS orders,
start_date FROM efforts WHERE adid={$req_vars['id']} AND id IN (SELECT effortid FROM ad_map WHERE textad={$req_vars['id']} AND position=1)
)
AS bar {$range} GROUP BY start_date ORDER BY start_date
i am trying to make that a subquery, but getting all kinds of different errors as i try.. anyone able to help me out?? I only need sum(orders) for the subquery, start_date isnt important..
pytrin wrote:You are nesting the subqueries way too deep.
Please post the structure of your table and what information you want to fetch.
sorry the query i posted is what i made to work with open flash charts, now i am trying to display the same data onto an html table.
I am trying to collect the total number of orders depending on an effortid that is in 2 different tables (transactions, and subscribers_transactions)
here is what i thought was right, but isnt.. probably not any help
(
SELECT sum(newsletter_orders) as totals FROM (
SELECT (
SELECT count(*) FROM (
SELECT id FROM transactions WHERE effort=efforts.id::text AND tx_type='N'
UNION ALL
SELECT id FROM subscribers_transactions WHERE effort=efforts.id::text AND tx_type='N'
) as foo
) as newsletter_orders FROM efforts WHERE adid=ads.id AND id IN (SELECT effortid FROM ad_map WHERE textad=ads.id AND position=1)
) as bar )
What is the primary key for the transactions table? I don't see the id column that appears in the query. Also, does transaction_id in the subscrip_trans table point to the primary key in the transactions table? is there a relationship between those two tables?
pytrin wrote:What is the primary key for the transactions table? I don't see the id column that appears in the query. Also, does transaction_id in the subscrip_trans table point to the primary key in the transactions table? is there a relationship between those two tables?
Transactions table is another table that we use for some of our services but in the process of getting everything inhouse (subscribers_transactions)
(
SELECT sum(newsletter_orders) FROM (
SELECT (
SELECT count(*) FROM (
SELECT id FROM transactions WHERE effort=efforts.id::text AND tx_type='N'
UNION ALL
SELECT id FROM subscribers_transactions WHERE effort=efforts.id::text AND tx_type='N'
) as foo
) as newsletter_orders FROM efforts WHERE adid=ads.id AND id IN (SELECT effortid FROM ad_map WHERE textad=ads.id AND position=1)
) as bar ) as totals,
What is the primary key for the transactions table? I don't see the id column that appears in the query. Also, does transaction_id in the subscrip_trans table point to the primary key in the transactions table? is there a relationship between those two tables?
I want to help, so I'd appreciate if you'd answer my questions. If you don't understand what I mean, please ask
What is the primary key for the transactions table? I don't see the id column that appears in the query. Also, does transaction_id in the subscrip_trans table point to the primary key in the transactions table? is there a relationship between those two tables?
I want to help, so I'd appreciate if you'd answer my questions. If you don't understand what I mean, please ask
there isnt one, the transactions table isnt 'ours' the guy who runs it is an idiot and as you can tell by his structure.