[SQL] Trouble making this query, a subquery

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!

Moderator: General Moderators

Post Reply
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

[SQL] Trouble making this query, a subquery

Post 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..
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: [SQL] Trouble making this query, a subquery

Post by Eran »

You are nesting the subqueries way too deep.
Please post the structure of your tables and what information you want to fetch.
Last edited by Eran on Thu Jun 03, 2010 1:53 pm, edited 1 time in total.
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: [SQL] Trouble making this query, a subquery

Post 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 )
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: [SQL] Trouble making this query, a subquery

Post by Eran »

Please post the structure of your tables
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: [SQL] Trouble making this query, a subquery

Post by bla5e »

:oops:
Last edited by bla5e on Thu Jun 03, 2010 4:40 pm, edited 1 time in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: [SQL] Trouble making this query, a subquery

Post 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?
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: [SQL] Trouble making this query, a subquery

Post 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)
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: [SQL] Trouble making this query, a subquery

Post 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,
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: [SQL] Trouble making this query, a subquery

Post 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
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: [SQL] Trouble making this query, a subquery

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: [SQL] Trouble making this query, a subquery

Post 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?
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: [SQL] Trouble making this query, a subquery

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