From: Martin Gainty Date: August 22 2012 12:30pm Subject: RE: help with correlated subquery List-Archive: http://lists.mysql.com/mysql/228048 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_eb538d59-b564-4d30-af98-bc89689fead9_" --_eb538d59-b564-4d30-af98-bc89689fead9_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 JOI= N Finally: Be aware FUNCTIONS such as AVG cast off indexing and should be avo= ided 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 th= at cause confusion like 'a' or 'foo' Develop in stages and write down what YOU EXPECT vs WHAT each query produce= s .. 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=2C OUTER JOI= N=2C LEFT JOIN or RIGHT JOIN operations will produce If the executed JOIN Statement does not produce expected results STOP and c= orrect the JOIN clause BEFORE incorporating more functionality Obfuscation and confusion can hopelessly sidetrack any intelligent analysis Martin=20 ______________________________________________=20 Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit= =E9 Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng= er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter= leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l= ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin= dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w= ir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes= pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat= isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e= ou la copie de ceci est interdite. Ce message sert =E0 l'information seule= ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d= onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation= =2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni= . > Date: Tue=2C 21 Aug 2012 20:26:51 -0600 > Subject: Re: help with correlated subquery > From: larry.martell@stripped > To: hsv@stripped > CC: mysql@stripped >=20 > On Tue=2C Aug 21=2C 2012 at 8:07 PM=2C wrote: > >>>>> 2012/08/21 16:35 -0600=2C Larry Martell >>>> > > I am trying to write a query that selects from both a correlated > > subquery and a table in the main query=2C and I'm having a lot of > > trouble getting the proper row count. I'm sure this is very simple=2C > > and I'm just missing it. I'll try and present a simple example. For > > this example=2C there are 27 rows=2C organized like this: > > > > > > mysql> select count(*)=2C target_name_id=2C ep=2C wafer_id from data_cs= t > > where target_name_id =3D 44 group by target_name_id=2C ep=2C wafer_id= =3B > > +----------+----------------+------+----------+ > > | count(*) | target_name_id | ep | wafer_id | > > +----------+----------------+------+----------+ > > | 6 | 44 | 1=2C1 | 16 | > > | 3 | 44 | 1=2C1 | 17 | > > | 6 | 44 | 1=2C2 | 16 | > > | 3 | 44 | 1=2C2 | 17 | > > | 6 | 44 | 1=2C3 | 16 | > > | 3 | 44 | 1=2C3 | 17 | > > +----------+----------------+------+----------+ > > 6 rows in set (0.00 sec) > > > > I need to get an average of a column grouped by target_name_id=2C ep as > > well as the average of the averages grouped by target_name_id=2C ep=2C > > wafer_id=2C and I also need the count of the rows in the target_name_id= =2C > > ep group. My query is getting the correct averages=2C but incorrect row > > counts: > > > > mysql> select count(*)=2C target_name_id=2C ep=2C avg(bottom)=2C avg(av= erages) > > from (select avg(bottom) as averages=2C target_name_id as t=2C ep as e > > from data_cst where target_name_id =3D 44 group by target_name_id=2C ep= =2C > > wafer_id) x=2C data_cst where target_name_id =3D t and ep =3D e group b= y > > target_name_id=2C ep=3B > > +----------+----------------+------+-------------+-----------------+ > > | count(*) | target_name_id | ep | avg(bottom) | avg(averages) | > > +----------+----------------+------+-------------+-----------------+ > > | 18 | 44 | 1=2C1 | 21.8056667 | 21.85458330000 | > > | 18 | 44 | 1=2C2 | 121.7984444 | 121.83983335000 | > > | 18 | 44 | 1=2C3 | 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=3B in effect=2C you are joining a row w= ith wafer_id 16 with a row with wafer_id 17=2C 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=2C as Ric= k James bids you do=2C is that one can add further conditions to it: > > > > select count(*)=2C target_name_id=2C ep=2C avg(bottom)=2C avg(averages) > > from (select avg(bottom) as averages=2C target_name_id=2C ep > > from data_cst > > where target_name_id =3D 44 > > group by target_name_id=2C ep=2C wafer_id) x > > JOIN data_cst > > ON target_name_id =3D x.target_name_id and ep =3D x.ep and wafer_id < x= .wafer_id > > group by target_name_id=2C ep > > > > The inequality=2C maybe=2C will give you what you want. >=20 > Not=2C wafer_id < x.wafer_id=2C but wafer_id =3D x.wafer_id - adding that > makes it work the way I want. Thanks!! >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql >=20 = --_eb538d59-b564-4d30-af98-bc89689fead9_--