Page 1 of 1
[SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 1:29 pm
by bla5e
Code: Select all
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..
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 1:34 pm
by Eran
You are nesting the subqueries way too deep.
Please post the structure of your tables and what information you want to fetch.
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 1:36 pm
by bla5e
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
Code: Select all
(
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 )
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 1:53 pm
by Eran
Please post the structure of your tables
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 2:22 pm
by bla5e
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 2:35 pm
by Eran
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?
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 2:43 pm
by bla5e
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)
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 2:49 pm
by bla5e
i am using this subquery right now, and its partially right.. sometimes
Code: Select all
(
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,
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 3:11 pm
by Eran
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
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 3:19 pm
by bla5e
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?
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.
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 3:23 pm
by Eran
But in your query you are selecting an 'id' column from that table. So what column should be selected instead to identify a row there?
Re: [SQL] Trouble making this query, a subquery
Posted: Thu Jun 03, 2010 4:17 pm
by bla5e
pytrin wrote:But in your query you are selecting an 'id' column from that table. So what column should be selected instead to identify a row there?
opps well i changed it to effort, but still getting same results (very odd it didnt break..wtf??..)