Current Position:Home > Opitmizing sql query using join

Opitmizing sql query using join

Update:11-30Source: network consolidation
Advertisement
Hello all,
I have the following query that needs to be written using Joins. I am not sure if this is the correct place to post this question
Tables used:
1. activities: (a_id, n_id, market, amount, act_type, act_number, act_seq) --the money paid by the registrant for the market.
2. market_reg (a_id, n_id, market, reg_stage) -- for each market there are registrants
3. market (market) -- stores market details
I need to get sum of total_payment_amount using the condition. I know the query is not opitmized and its not the correct way to get info. I was
wondering if you guys can suggest me a query that will pull the information by using joins
SELECT a.a_id, a.n_id, SUM (a.total_payment_amount)
FROM activities a
WHERE a.market = 'marketname'
AND a.a_id||a.n_id IN
(SELECT mr.a_id||mr.n_id
FROM market_reg mr
WHERE mr.market = 'marketname'
AND mr.reg_stage = 'P'
AND mr.n_id <> 0)
AND (a.act_type = 'A'
OR (a.act_type IS NULL
AND a.act_number||act_seq IN
( SELECT a1.act_number||a1.act_seq
FROM activities a1
WHERE a1.market = 'marketname'
GROUP BY a1.act_number||a1.act_seq
HAVING COUNT (a1.act_number||a1.act_seq) = 1)))
GROUP BY a.a_id, a.n_id;
Thanks

The Best Answer

Advertisement
Hi,
I see you have already re-posted this on a more appropriate forum, the SQL and PL/SQL forum.
Thanks,
Gary
  • Opitmizing sql query using join Update:11-30

    Hello all, I have the following query that needs to be written using Joins. I am not sure if this is the correct place to post this question Tables used: 1. activities: (a_id, n_id, market, amount, act_type, act_number, act_seq) --the money paid by t

  • Get column names into a VB program, for an SQL query that joins tables Update:11-30

    use [mydatabase] select * from InvNum inum left join _btblInvoiceLines ilin on inum.autoindex = ilin.iinvoiceid left join stkitem s on ilin.istockcodeid = s.stocklink where InvNumber = 'IVQ0834' or InvNumber = 'IVQ0835' I need to get out the column n

  • About SQL Query using joins Update:11-30

    Hi, I want to write a SQL statement using Joins. The base table "A" has about 100 fields and the join table "B" a few. I would like to fetch all fields from table "A" and a few from table "B". Is there a way to writ

  • SQL Query to Join by Comma  - Help an assistance Update:11-30

    Hi, Thanks for your help in advance. Requirement is as follows. Table1 ======= Col1 Col2 ============ 1 John 2 Jocky 3 Silk Table2 ======== Col1 Col2 ========= 1 John, Marry, Joseph 2 Silk, David 3 Jocky, Prem I need an sql query where the join condi

  • SQL query -- self-join question? Update:11-30

    SQL> l 1* select originator,destination,oaddress,daddress from (select * from activity where rownum<=3) SQL> / 10099 10004 16196344392 16199375530 10064 10002 18454644069 18456563415 10065 10006 18302650166 16416609306 looking at the above query,

  • SQL query for join table and multiple values Update:11-30

    Trying to join two tables , Emphours and EmpStatus to get result which gives each emplyees hour  worked each day in past  say 1 year in what status. I need result similar to table 3 , Hours Can also be grouped per week all I need Is Each employees ho

  • Performance tuning in SQL query using join of views Update:10-11

    Hi, Am trying to tune a query of the format select ........ from view1,view2 where view1.keyfield = view2.keyfield The base tables of the views view1 and view2 have indexes specified on 'keyfield'. However, when I do an explain plan of the query, Ful

  • SQL Query Using Join Update:11-30

    Hi, I am trying to join two tables using joining. SQL> select * from test10; ID 1 2 SQL> select * from test11; ID 1 3 4 My expected output is ID 1 2 3 4 I am able to do this using "Union" SQL> select * from test10 2 union 3 select * fro

  • Sql Query with Join Update:11-30

    I have a table called logon table which has some columns like userid, Firstname, lastname, email and Parent. Typically looks like this userid Firstname lastname email      parent 101 Bob     Dan          [email protected]     309 102     Riz     Bob 

  • SQL Query with Joins Update:11-30

    Hi..I'm having trouble wiht my query. I'm getting way too many rows when displayed. I should have either 12 or 24 row...I think 12, but not sure. What I am getting is 228 rows displayed. This is all my code, including the select statment that dsiplay