List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:August 22 2012 12:30pm
Subject:RE: help with correlated subquery
View as plain text  
assign realistic alias names
OuterJoin should be called OuterJoin
InnerJoin should be called InnerJoin

If you want only the most restricitive criteria that match resultsets from both select
statements use INNER JOIN
if you want all results from both resultsets (cartesian JOIN) use OUTER JOIN

Finally: Be aware FUNCTIONS such as AVG cast off indexing and should be avoided unless the
FUNCTION(columnName) itself is indexed
GROUP BY re-arranges your query so its best to introduce GROUP BY in stages

use realistic alias names like Dept and EmployeeNumber and avoid aliases that cause
confusion like 'a' or 'foo'

Develop in stages and write down what YOU EXPECT vs WHAT each query produces ..
If the Individual Select doesnt produce expected results..STOP and correct the SELECT
Statement

Diagram out what you expect results from any of the INNER JOIN, OUTER JOIN, LEFT JOIN or
RIGHT JOIN operations will produce
If the executed JOIN Statement does not produce expected results STOP and correct the JOIN
clause BEFORE incorporating more functionality

Obfuscation and confusion can hopelessly sidetrack any intelligent analysis
Martin 
______________________________________________ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so
bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer
Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und
entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous
n'êtes pas le destinataire prévu, nous te demandons avec bonté que
pour satisfaire informez l'expéditeur. N'importe quelle diffusion non
autorisée ou la copie de ceci est interdite. Ce message sert à
l'information seulement et n'aura pas n'importe quel effet légalement obligatoire.
Étant donné que les email peuvent facilement être sujets à la
manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu
fourni.


> Date: Tue, 21 Aug 2012 20:26:51 -0600
> Subject: Re: help with correlated subquery
> From: larry.martell@stripped
> To: hsv@stripped
> CC: mysql@stripped
> 
> On Tue, Aug 21, 2012 at 8:07 PM,  <hsv@stripped> wrote:
> >>>>> 2012/08/21 16:35 -0600, Larry Martell >>>>
> > I am trying to write a query that selects from both a correlated
> > subquery and a table in the main query, and I'm having a lot of
> > trouble getting the proper row count. I'm sure this is very simple,
> > and I'm just missing it. I'll try and present a simple example. For
> > this example, there are 27 rows, organized like this:
> >
> >
> > mysql> select count(*), target_name_id, ep, wafer_id from data_cst
> > where target_name_id = 44 group by target_name_id, ep, wafer_id;
> > +----------+----------------+------+----------+
> > | count(*) | target_name_id | ep   | wafer_id |
> > +----------+----------------+------+----------+
> > |        6 |             44 | 1,1  |       16 |
> > |        3 |             44 | 1,1  |       17 |
> > |        6 |             44 | 1,2  |       16 |
> > |        3 |             44 | 1,2  |       17 |
> > |        6 |             44 | 1,3  |       16 |
> > |        3 |             44 | 1,3  |       17 |
> > +----------+----------------+------+----------+
> > 6 rows in set (0.00 sec)
> >
> > I need to get an average of a column grouped by target_name_id, ep as
> > well as the average of the averages grouped by target_name_id, ep,
> > wafer_id, and I also need the count of the rows in the target_name_id,
> > ep group. My query is getting the correct averages, but incorrect row
> > counts:
> >
> > mysql> select count(*), target_name_id, ep, avg(bottom), avg(averages)
> > from (select avg(bottom) as averages, target_name_id as t, ep as e
> > from data_cst where target_name_id = 44 group by target_name_id, ep,
> > wafer_id) x, data_cst where target_name_id = t and ep = e group by
> > target_name_id, ep;
> > +----------+----------------+------+-------------+-----------------+
> > | count(*) | target_name_id | ep   | avg(bottom) | avg(averages)   |
> > +----------+----------------+------+-------------+-----------------+
> > |       18 |             44 | 1,1  |  21.8056667 |  21.85458330000 |
> > |       18 |             44 | 1,2  | 121.7984444 | 121.83983335000 |
> > |       18 |             44 | 1,3  | 349.7634444 | 349.75016665000 |
> > +----------+----------------+------+-------------+-----------------+
> > 3 rows in set (0.01 sec)
> >
> > The count for each row should be 9. What do I need in my count() to be
> > counting the right thing?
> > <<<<<<<<
> > Your trouble lys in the joining; in effect, you are joining a row with wafer_id
> 16 with a row with wafer_id 17, and also a row with wafer_id 17 with a row with wafer_id
> 16.
> >
> > A further advantage to using the now standard form of joining, as Rick James
> bids you do, is that one can add further conditions to it:
> >
> > select count(*), target_name_id, ep, avg(bottom), avg(averages)
> > from (select avg(bottom) as averages, target_name_id, ep
> >         from data_cst
> >         where target_name_id = 44
> >         group by target_name_id, ep, wafer_id) x
> > JOIN data_cst
> > ON target_name_id = x.target_name_id and ep = x.ep and wafer_id < x.wafer_id
> > group by target_name_id, ep
> >
> > The inequality, maybe, will give you what you want.
> 
> Not, wafer_id < x.wafer_id, but wafer_id = x.wafer_id - adding that
> makes it work the way I want. Thanks!!
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
> 
 		 	   		  
Thread
help with correlated subqueryLarry Martell21 Aug
  • RE: help with correlated subqueryRick James21 Aug
    • Re: help with correlated subqueryLarry Martell22 Aug
  • Re: help with correlated subqueryhsv22 Aug
    • Re: help with correlated subqueryLarry Martell22 Aug
      • RE: help with correlated subqueryRick James22 Aug
      • Re: help with correlated subqueryLarry Martell23 Aug
        • Re: help with correlated subqueryLarry Martell23 Aug
Re: help with correlated subqueryLarry Martell22 Aug
RE: help with correlated subqueryMartin Gainty22 Aug
  • Re: help with correlated subqueryShawn Green22 Aug
    • Re: JOIN, JOIN, JOINhsv23 Aug