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
:oops:

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??..)