From jsacksteder@gmail.com Tue Sep  27 00:34:34 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id DED34D8595
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 26 Sep 2005 21:34:34 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 47074-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 00:34:09 +0000 (GMT)
Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.195])
	by svr1.postgresql.org (Postfix) with ESMTP id 34E79D97F0
	for <pgsql-sql@postgresql.org>; Mon, 26 Sep 2005 21:34:10 -0300 (ADT)
Received: by xproxy.gmail.com with SMTP id s14so1251466wxc
	for <pgsql-sql@postgresql.org>; Mon, 26 Sep 2005 17:34:14 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type;
	b=RFrbgazHMN4JCsTp8ichilb281C2usUqEZAtVm/n+xeJhgJ5quBJDZyWlGFNdBM1WH+VdCMLuhX4dF0dHvm+5PrYlSFXUpxFYA0Iy3ZD6cnGy7EqrGu2ythsmey1fCfEFAiXZnxnpy1qRmTc0Sun9cNKDAaM51VzvxyDKsCKmv0=
Received: by 10.70.34.10 with SMTP id h10mr2633148wxh;
	Mon, 26 Sep 2005 17:34:14 -0700 (PDT)
Received: by 10.70.87.19 with HTTP; Mon, 26 Sep 2005 17:34:14 -0700 (PDT)
Message-ID: <51c8a7be050926173448a22f8@mail.gmail.com>
Date: Mon, 26 Sep 2005 20:34:14 -0400
From: jeff sacksteder <jsacksteder@gmail.com>
Reply-To: jeff sacksteder <jsacksteder@gmail.com>
To: pgsql-sql@postgresql.org
Subject: how to do 'deep queries'?
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_17994_10686733.1127781254645"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.79 required=5 tests=[AWL=0.679, HTML_40_50=0.086,
	HTML_MESSAGE=0.001, RCVD_BY_IP=0.024]
X-Spam-Level: 
X-Archive-Number: 200509/256
X-Sequence-Number: 22803

------=_Part_17994_10686733.1127781254645
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Is there supported syntax to do 'deep' queries? That is where A relates to =
B
relates to C, returning fields from each table?

This doesn't seem to work. Is there a google-able term for this sort of
query?

select
foo.aaa,
bar.bbb,
baz.ccc

from
foo,bar,baz

where
foo.bar_id =3D bar.id <http://bar.id>
and
bar.baz_id =3D baz.id <http://baz.id>

------=_Part_17994_10686733.1127781254645
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Is there supported syntax to do 'deep' queries? That is where A relates to =
B relates to C, returning fields from each table?<br>
<br>
This doesn't seem to work. Is there a google-able term for this sort of que=
ry?<br>
<br>
select <br>
&nbsp;&nbsp; foo.aaa,<br>
&nbsp;&nbsp; bar.bbb,<br>
&nbsp;&nbsp; baz.ccc<br>
<br>
from<br>
&nbsp;&nbsp; foo,bar,baz<br>
<br>
where <br>
&nbsp;&nbsp; foo.bar_id =3D <a href=3D"http://bar.id">bar.id</a><br>
and<br>
&nbsp;&nbsp; bar.baz_id =3D <a href=3D"http://baz.id">baz.id</a><br>
<br>
<br>
<br>

------=_Part_17994_10686733.1127781254645--



From Ben.Stewart@au.bosch.com Tue Sep  27 00:45:19 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 339D0D97E2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 26 Sep 2005 21:45:19 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 52469-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 00:45:14 +0000 (GMT)
Received: from smtp-v.fe.bosch.de (smtp-v.fe.bosch.de [139.15.237.2])
	by svr1.postgresql.org (Postfix) with ESMTP id C9A61D9827
	for <pgsql-sql@postgresql.org>; Mon, 26 Sep 2005 21:45:14 -0300 (ADT)
Received: from mta3.fe.internet.bosch.com (unknown [10.4.98.30])
	by imta7.fe.bosch.de (Postfix) with ESMTP id 1BFA73C015
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 02:45:17 +0200 (CEST)
Received: from mailgate.cl.internet.bosch.com
	(virusscan6.fe.internet.bosch.com [10.4.98.11])
	by mta3.fe.internet.bosch.com (Postfix) with ESMTP id 48C6E8C090
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 02:45:16 +0200 (CEST)
Received: from cl-mail01.au.bosch.com (unknown [10.23.5.53])
	by mailgate.cl.internet.bosch.com (Postfix) with ESMTP id 3E02252837
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 10:45:14 +1000 (EST)
X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0
content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: how to do 'deep queries'?
Date: Tue, 27 Sep 2005 10:45:13 +1000
Message-ID: <E253BDD7F008244585AEE87AF8F0224F116C7A6A@cl-mail01.au.bosch.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] how to do 'deep queries'?
Thread-Index: AcXC/B5F/CNNZ1wIRmCWmYD2SGqaMAAAFYrg
From: "Stewart Ben (RBAU/EQS4) *" <Ben.Stewart@au.bosch.com>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.604 required=5 tests=[AWL=0.230,
	DNS_FROM_RFC_ABUSE=0.374]
X-Spam-Level: 
X-Archive-Number: 200509/258
X-Sequence-Number: 22805

> Is there supported syntax to do 'deep' queries? That is where=20
> A relates to B relates to C, returning fields from each table?
>=20
> This doesn't seem to work. Is there a google-able term for=20
> this sort of query?
>=20
> select=20
>    foo.aaa,
>    bar.bbb,
>    baz.ccc
>=20
> from
>    foo,bar,baz
>=20
> where=20
>    foo.bar_id =3D bar.id
> and
>    bar.baz_id =3D baz.id

This works for me..

SELECT table1.state, table2.coursename, table3.firstname
  FROM backend.enrolments table1, backend.courses table2, backend.users
table3
 WHERE table1.user =3D table3.employeeno
   AND table1.course =3D table2.courseid;

What errors are you getting?

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
Tel: +61 3 9541-7002 Fax: +61 3 9541-7700
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/=20



From amolinaro@wgen.net Tue Sep  27 00:58:43 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5A50BD9818
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 26 Sep 2005 21:58:43 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 54022-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 00:58:37 +0000 (GMT)
Received: from exchange.wgen.net (exchange.wgen.net [216.194.17.194])
	by svr1.postgresql.org (Postfix) with ESMTP id 4CEC8D97E2
	for <pgsql-sql@postgresql.org>; Mon, 26 Sep 2005 21:58:38 -0300 (ADT)
X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C5C2FE.997CC856"
Subject: Re: how to do 'deep queries'?
Date: Mon, 26 Sep 2005 20:58:40 -0400
Message-ID: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182570@wgexch01.wgenhq.net>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] how to do 'deep queries'?
Thread-Index: AcXC/GiNrdRKhS+7TdKZysbHebuNhgAAWCKw
From: "Anthony Molinaro" <amolinaro@wgen.net>
To: "jeff sacksteder" <jsacksteder@gmail.com>,
	<pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.191 required=5 tests=[AWL=-0.166,
	HTML_MESSAGE=0.001, HTML_TEXT_AFTER_BODY=0.151,
	HTML_TEXT_AFTER_HTML=0.205]
X-Spam-Level: 
X-Archive-Number: 200509/259
X-Sequence-Number: 22806

This is a multi-part message in MIME format.

------_=_NextPart_001_01C5C2FE.997CC856
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

that query is 100% correct.
=20
it's just an equijoin (a type of inner join) between 3 tables.
=20
the syntax you show is how queries should be written and is more
representative of what a joins between relations really are:
Cartesian products with filters applied
=20
the ansi syntax, the explicit JOIN ... ON  stuff is (imho) unnecessary,
useful only for outer joins since all the vendors did it differently.
=20
what you have will work for postgreSQL, I used the syntax you show in my
book
for every single join recipe except for outjoins.
=20
are you seeing errors?
=20
regards,
  Anthony
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of jeff sacksteder
Sent: Monday, September 26, 2005 8:34 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] how to do 'deep queries'?
=20
Is there supported syntax to do 'deep' queries? That is where A relates
to B relates to C, returning fields from each table?

This doesn't seem to work. Is there a google-able term for this sort of
query?

select=20
   foo.aaa,
   bar.bbb,
   baz.ccc

from
   foo,bar,baz

where=20
   foo.bar_id =3D bar.id
and
   bar.baz_id =3D baz.id




------_=_NextPart_001_01C5C2FE.997CC856
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DProgId content=3DWord.Document>
<meta name=3DGenerator content=3D"Microsoft Word 10">
<meta name=3DOriginator content=3D"Microsoft Word 10">
<link rel=3DFile-List href=3D"cid:filelist.xml@01C5C2DD.13185590">
<o:SmartTagType =
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
 name=3D"time"/>
<o:SmartTagType =
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
 name=3D"date"/>
<!--[if gte mso 9]><xml>
 <o:OfficeDocumentSettings>
  <o:DoNotRelyOnCSS/>
 </o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
 <w:WordDocument>
  <w:Zoom>120</w:Zoom>
  <w:DocumentKind>DocumentEmail</w:DocumentKind>
  <w:EnvelopeVis/>
  <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
 </w:WordDocument>
</xml><![endif]--><!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;
	mso-font-charset:0;
	mso-generic-font-family:swiss;
	mso-font-pitch:variable;
	mso-font-signature:553679495 -2147483648 8 0 66047 0;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{mso-style-parent:"";
	margin:0in;
	margin-bottom:.0001pt;
	mso-pagination:widow-orphan;
	font-size:12.0pt;
	font-family:"Times New Roman";
	mso-fareast-font-family:"Times New Roman";}
h3
	{mso-style-update:auto;
	mso-style-next:Normal;
	margin-top:12.0pt;
	margin-right:0in;
	margin-bottom:3.0pt;
	margin-left:0in;
	text-align:justify;
	line-height:12.0pt;
	mso-pagination:widow-orphan;
	page-break-after:avoid;
	mso-outline-level:3;
	font-size:10.0pt;
	mso-bidi-font-size:13.0pt;
	font-family:"Times New Roman";
	mso-bidi-font-family:Arial;
	color:red;}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;
	text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
	{color:blue;
	text-decoration:underline;
	text-underline:single;}
span.EmailStyle17
	{mso-style-type:personal-reply;
	mso-style-noshow:yes;
	mso-ansi-font-size:10.0pt;
	mso-bidi-font-size:10.0pt;
	font-family:Arial;
	mso-ascii-font-family:Arial;
	mso-hansi-font-family:Arial;
	mso-bidi-font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;
	mso-header-margin:.5in;
	mso-footer-margin:.5in;
	mso-paper-source:0;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 10]>
<style>
 /* Style Definitions */=20
 table.MsoNormalTable
	{mso-style-name:"Table Normal";
	mso-tstyle-rowband-size:0;
	mso-tstyle-colband-size:0;
	mso-style-noshow:yes;
	mso-style-parent:"";
	mso-padding-alt:0in 5.4pt 0in 5.4pt;
	mso-para-margin:0in;
	mso-para-margin-bottom:.0001pt;
	mso-pagination:widow-orphan;
	font-size:10.0pt;
	font-family:"Times New Roman";}
</style>
<![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dblue style=3D'tab-interval:.5in'>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>that query is 100% =
correct.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>it&#8217;s just an equijoin (a type =
of
inner join) between 3 tables.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>the syntax you show is how queries =
should
be written and is more<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>representative of what a joins =
between
relations really are:<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Cartesian products with filters =
applied<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>the ansi syntax, the explicit JOIN =
&#8230;
ON<span style=3D'mso-spacerun:yes'>&nbsp; </span>stuff is (imho) =
unnecessary,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>useful only for outer joins since =
all the
vendors did it differently.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>what you have will work for =
postgreSQL, I used
the syntax you show in my book<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>for every single join recipe except =
for
outjoins.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>are you seeing =
errors?<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>regards,<o:p></o:p></span></font></p=
>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><span =
style=3D'mso-spacerun:yes'>&nbsp;
</span>Anthony<o:p></o:p></span></font></p>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DTahoma><span
style=3D'font-size:10.0pt;font-family:Tahoma'>-----Original =
Message-----<br>
<b><span style=3D'font-weight:bold'>From:</span></b>
pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] =
<b><span
style=3D'font-weight:bold'>On Behalf Of </span></b>jeff sacksteder<br>
<b><span style=3D'font-weight:bold'>Sent:</span></b> =
</span></font><st1:date
Month=3D"9" Day=3D"26" Year=3D"2005"><font size=3D2 face=3DTahoma><span =
style=3D'font-size:
 10.0pt;font-family:Tahoma'>Monday, September 26, =
2005</span></font></st1:date><font
size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;font-family:Tahoma'> </span></font><st1:time
Hour=3D"20" Minute=3D"34"><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
 font-family:Tahoma'>8:34 PM</span></font></st1:time><font size=3D2 =
face=3DTahoma><span
style=3D'font-size:10.0pt;font-family:Tahoma'><br>
<b><span style=3D'font-weight:bold'>To:</span></b> =
pgsql-sql@postgresql.org<br>
<b><span style=3D'font-weight:bold'>Subject:</span></b> [SQL] how to do =
'deep
queries'?</span></font></p>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
12.0pt;margin-left:.5in'><font size=3D3 face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'>Is there supported syntax to do 'deep' =
queries? That
is where A relates to B relates to C, returning fields from each =
table?<br>
<br>
This doesn't seem to work. Is there a google-able term for this sort of =
query?<br>
<br>
select <br>
&nbsp;&nbsp; foo.aaa,<br>
&nbsp;&nbsp; bar.bbb,<br>
&nbsp;&nbsp; baz.ccc<br>
<br>
from<br>
&nbsp;&nbsp; foo,bar,baz<br>
<br>
where <br>
&nbsp;&nbsp; foo.bar_id =3D <a href=3D"http://bar.id">bar.id</a><br>
and<br>
&nbsp;&nbsp; bar.baz_id =3D <a href=3D"http://baz.id">baz.id</a><br>
<br style=3D'mso-special-character:line-break'>
<![if !supportLineBreakNewLine]><br =
style=3D'mso-special-character:line-break'>
<![endif]><o:p></o:p></span></font></p>

</div>

</body>

</html>
=00
------_=_NextPart_001_01C5C2FE.997CC856--



From jsacksteder@gmail.com Tue Sep  27 03:35:57 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4C69FD977F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 00:35:57 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94752-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 03:35:55 +0000 (GMT)
Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 9B151D9581
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 00:35:54 -0300 (ADT)
Received: by xproxy.gmail.com with SMTP id s14so1273804wxc
	for <pgsql-sql@postgresql.org>; Mon, 26 Sep 2005 20:35:54 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=rgC8BplTzK1/j0AOxPxAjOVeyYeaRKgw2EHfoJo1X1MDhWCXfXk8jcSXAukJVVwXdPt+semKpGBNPswz5t9gcbU+g1NsVY/CtqkmxRQv8h1klyOIb/ZwZRUIvccGmHWC5b62xxLAHh2PdpvlLp5FzdeqXmztCngG7FcJQgEz/dw=
Received: by 10.70.49.12 with SMTP id w12mr2619655wxw;
	Mon, 26 Sep 2005 20:35:54 -0700 (PDT)
Received: by 10.70.87.19 with HTTP; Mon, 26 Sep 2005 20:35:54 -0700 (PDT)
Message-ID: <51c8a7be05092620354f0b2449@mail.gmail.com>
Date: Mon, 26 Sep 2005 23:35:54 -0400
From: jeff sacksteder <jsacksteder@gmail.com>
Reply-To: jeff sacksteder <jsacksteder@gmail.com>
To: Anthony Molinaro <amolinaro@wgen.net>
Subject: Re: how to do 'deep queries'?
Cc: pgsql-sql@postgresql.org
In-Reply-To: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182570@wgexch01.wgenhq.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_18580_14701365.1127792154577"
References: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182570@wgexch01.wgenhq.net>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.946 required=5 tests=[AWL=0.070, HTML_00_10=0.138,
	HTML_MESSAGE=0.001, HTML_SHORT_LENGTH=0.713, RCVD_BY_IP=0.024]
X-Spam-Level: 
X-Archive-Number: 200509/262
X-Sequence-Number: 22809

------=_Part_18580_14701365.1127792154577
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Nevermind. It's late here and I'm not thinking clearly. Problem solved.

------=_Part_18580_14701365.1127792154577
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Nevermind. It's late here and I'm not thinking clearly. Problem solved.<br>

------=_Part_18580_14701365.1127792154577--



From daryl@brandywine.com Tue Sep  27 13:22:42 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E796DD98E9
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 10:22:42 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 53897-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 13:22:31 +0000 (GMT)
Received: from website.brandywine.com (mailcheck.brandywine.com
	[146.145.113.9])
	by svr1.postgresql.org (Postfix) with ESMTP id 64DECD9913
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 10:22:20 -0300 (ADT)
X-eSCM-MailFrom: <daryl@brandywine.com>
Received: from BAM_EXCHANGE (10.1.0.100) by website.brandywine.com (10.1.0.9);
	Tue, 27 September 2005 09:25:07 -0400
Received: from [10.1.0.60] (gen-wayne [10.1.0.60]) by
	bam_exchange.brandywine.com with SMTP (Microsoft Exchange
	Internet Mail Service Version 5.5.2653.13)
	id R6ZYXVDN; Tue, 27 Sep 2005 09:22:49 -0400
Message-ID: <433947F6.7060106@brandywine.com>
Date: Tue, 27 Sep 2005 09:24:06 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: how to do 'deep queries'?
References: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182570@wgexch01.wgenhq.net>
In-Reply-To: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182570@wgexch01.wgenhq.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 required=5 tests=[none]
X-Spam-Level: 
X-Archive-Number: 200509/264
X-Sequence-Number: 22811

Anthony Molinaro wrote:
> that query is 100% correct.
>  
> it's just an equijoin (a type of inner join) between 3 tables.
>  
> the syntax you show is how queries should be written and is more
> representative of what a joins between relations really are:
> Cartesian products with filters applied
>  
> the ansi syntax, the explicit JOIN ... ON  stuff is (imho) unnecessary,
> useful only for outer joins since all the vendors did it differently.
>

Whether you feel that is unnecessary or not, it *is* the ANSI Standard 
and is thus, by definition, "how queries should be written."

In addition to cleaning up the outer join issue, it was added to make 
the *intention* of the query clearer.

Because others are likely to read your query many more times than you 
write it, clarity of intent *is* important.

> what you have will work for postgreSQL, I used the syntax you show in my
> book
> for every single join recipe except for outjoins.
>  
> are you seeing errors?
>  
> regards,
>   Anthony

[original snipped]

-- 
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
     -- Colonel Henry Knox, 1776




From amolinaro@wgen.net Tue Sep  27 14:23:27 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 1A1CBD991F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 11:23:27 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94279-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 14:23:18 +0000 (GMT)
Received: from exchange.wgen.net (exchange.wgen.net [216.194.17.194])
	by svr1.postgresql.org (Postfix) with ESMTP id D50B4D98EF
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 11:23:18 -0300 (ADT)
X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: how to do 'deep queries'?
Date: Tue, 27 Sep 2005 10:23:19 -0400
Message-ID: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182583@wgexch01.wgenhq.net>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] how to do 'deep queries'?
Thread-Index: AcXDaCGK59tgLdAFQ1Wb/vD/V+rAewAAXPDw
From: "Anthony Molinaro" <amolinaro@wgen.net>
To: <daryl@brandywine.com>, <pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.036 required=5 tests=[AWL=0.036]
X-Spam-Level: 
X-Archive-Number: 200509/267
X-Sequence-Number: 22814

Daryl,
=20
> Whether you feel that is unnecessary or not, it *is* the ANSI Standard

> and is thus, by definition, "how queries should be written." =20
 =20
I disagree 100%.  Oracle and db2 introduced window functions years
before
Ansi added them. Should we not have used them? It absurd to avoid using
a feature cuz it's not ansi.

Honestly, Don't be a slave to ansi, you miss out on all the great
vendor specific functionality *that you're already paying for*

> it was added to make the *intention* of the query clearer.

More clearer to whom?=20

Certainly not developers who have been working for many years=20
using the old syntax.=20

The intention of the old syntax is perfect. Realize that the problem is
not the old syntax, the problem is the watered down database field
today.=20
I see this more and more with each interview I conduct looking=20
for dba's and developers.

You know, it used to be that database developers=20
had a solid background in math and relational theory.  =20
Sadly, that's not the case anymore...

   select * from a,b where a.id=3Db.id =20

Suggests a Cartesian product between two relations then a filter to keep
only matching rows.=20

That's a join. And that syntax is a *perfect* representation of it.

So to whom is ansi more clear? To the person who knows nothing about
databases and decided one day to get a certification and call themselves
an expert?

Or maybe the person who decided one day on a whim to get into databases
and not realize that tons of code from the prior decade use the old
style syntax?

> Because others are likely to read your query many more times than you=20
> write it, clarity of intent *is* important.

I've never worked in a place that used ANSI only syntax and I've never
had a problem with clarity nor any developers I've worked with.
So, I don't at all get what you're saying...

Old style is short and sweet and perfect.=20
Ansi dumbed it down, that's the bottom line.
And for people who've been developing for sometime,
It's wholly unnecessary.

Regards,
  Anthony

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Daryl Richter
Sent: Tuesday, September 27, 2005 9:24 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] how to do 'deep queries'?

Anthony Molinaro wrote:
> that query is 100% correct.
> =20
> it's just an equijoin (a type of inner join) between 3 tables.
> =20
> the syntax you show is how queries should be written and is more
> representative of what a joins between relations really are:
> Cartesian products with filters applied
> =20
> the ansi syntax, the explicit JOIN ... ON  stuff is (imho)
unnecessary,
> useful only for outer joins since all the vendors did it differently.
>

Whether you feel that is unnecessary or not, it *is* the ANSI Standard=20
and is thus, by definition, "how queries should be written."

In addition to cleaning up the outer join issue, it was added to make=20
the *intention* of the query clearer.

Because others are likely to read your query many more times than you=20
write it, clarity of intent *is* important.

> what you have will work for postgreSQL, I used the syntax you show in
my
> book
> for every single join recipe except for outjoins.
> =20
> are you seeing errors?
> =20
> regards,
>   Anthony

[original snipped]

--=20
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
     -- Colonel Henry Knox, 1776


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster



From daryl@brandywine.com Tue Sep  27 15:33:58 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 10969D98DB
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 12:33:58 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 41463-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 15:33:54 +0000 (GMT)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by svr1.postgresql.org (Postfix) with ESMTP id E071CD9201
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 12:33:53 -0300 (ADT)
Received: from website.brandywine.com (mailcheck.brandywine.com
	[146.145.113.9])
	by svr4.postgresql.org (Postfix) with ESMTP id 220C15AF5EA
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 15:33:53 +0000 (GMT)
X-eSCM-MailFrom: <daryl@brandywine.com>
Received: from BAM_EXCHANGE (10.1.0.100) by website.brandywine.com (10.1.0.9);
	Tue, 27 September 2005 11:36:32 -0400
Received: from [10.1.0.60] (gen-wayne [10.1.0.60]) by
	bam_exchange.brandywine.com with SMTP (Microsoft Exchange
	Internet Mail Service Version 5.5.2653.13)
	id R6ZYXVK4; Tue, 27 Sep 2005 11:34:11 -0400
Message-ID: <433966C1.80008@brandywine.com>
Date: Tue, 27 Sep 2005 11:35:29 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Anthony Molinaro <amolinaro@wgen.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to do 'deep queries'?
References: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182583@wgexch01.wgenhq.net>
In-Reply-To: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182583@wgexch01.wgenhq.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 required=5 tests=[none]
X-Spam-Level: 
X-Archive-Number: 200509/272
X-Sequence-Number: 22819

Anthony Molinaro wrote:
 > Daryl,
 >
 >
 >>Whether you feel that is unnecessary or not, it *is* the ANSI Standard
 >
 >
 >>and is thus, by definition, "how queries should be written."
 >
 >
 > I disagree 100%.  Oracle and db2 introduced window functions years
 > before
 > Ansi added them. Should we not have used them? It absurd to avoid using
 > a feature cuz it's not ansi.
 >

Of course it would be absurd, I have not suggested otherwise.  Joins are 
not a *new* feature.

 > Honestly, Don't be a slave to ansi, you miss out on all the great
 > vendor specific functionality *that you're already paying for*
 >
 >
 >>it was added to make the *intention* of the query clearer.
 >
 >
 > More clearer to whom?
 >
 > Certainly not developers who have been working for many years
 > using the old syntax.
 >
 > The intention of the old syntax is perfect. Realize that the problem is
 > not the old syntax, the problem is the watered down database field
 > today.
 > I see this more and more with each interview I conduct looking
 > for dba's and developers.
 >

I generally agree with your assessment of the state of database 
knowledge (particularly re developers).  It is, however, the reality we 
live in.

[snipped nostalgia and back-patting]

 > I've never worked in a place that used ANSI only syntax and I've never
 > had a problem with clarity nor any developers I've worked with.
 > So, I don't at all get what you're saying...

 > Old style is short and sweet and perfect.
 > Ansi dumbed it down, that's the bottom line.
 > And for people who've been developing for sometime,
 > It's wholly unnecessary.
 >

Well, perhaps you will one day and a developer will hose your server 
with a "accidental" cross join and then you will understand.

But hopefully not.  ;)

 > Regards,
 >   Anthony
 >

[rest snipped]

-- 
Daryl
Director of Technology

((         Brandywine Asset Management          )
  ( "Expanding the Science of Global Investing"  )
  (          http://www.brandywine.com           ))





From amolinaro@wgen.net Tue Sep  27 15:44:24 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2682BD9945
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 12:44:24 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 40566-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Sep 2005 15:44:16 +0000 (GMT)
Received: from exchange.wgen.net (exchange.wgen.net [216.194.17.194])
	by svr1.postgresql.org (Postfix) with ESMTP id 8170BD993A
	for <pgsql-sql@postgresql.org>; Tue, 27 Sep 2005 12:44:14 -0300 (ADT)
X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: how to do 'deep queries'?
Date: Tue, 27 Sep 2005 11:44:11 -0400
Message-ID: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182585@wgexch01.wgenhq.net>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] how to do 'deep queries'?
Thread-Index: AcXDeNxQMJH+gLqpQ1mOl6K+hJt0jwAAL72w
From: "Anthony Molinaro" <amolinaro@wgen.net>
To: <daryl@brandywine.com>
Cc: <pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.032 required=5 tests=[AWL=0.032]
X-Spam-Level: 
X-Archive-Number: 200509/273
X-Sequence-Number: 22820

> Well, perhaps you will one day and a developer will hose your server=20
> with a "accidental" cross join and then you will understand.

Hehe :))=20

hey man, that's what testing and code review is all about
(dev teams still do that don't they?)

Accidental cartesians don't get to production ;)

Regards,
  Anthony

-----Original Message-----
From: Daryl Richter [mailto:daryl@brandywine.com]=20
Sent: Tuesday, September 27, 2005 11:35 AM
To: Anthony Molinaro
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] how to do 'deep queries'?

Anthony Molinaro wrote:
 > Daryl,
 >
 >
 >>Whether you feel that is unnecessary or not, it *is* the ANSI
Standard
 >
 >
 >>and is thus, by definition, "how queries should be written."
 >
 >
 > I disagree 100%.  Oracle and db2 introduced window functions years
 > before
 > Ansi added them. Should we not have used them? It absurd to avoid
using
 > a feature cuz it's not ansi.
 >

Of course it would be absurd, I have not suggested otherwise.  Joins are

not a *new* feature.

 > Honestly, Don't be a slave to ansi, you miss out on all the great
 > vendor specific functionality *that you're already paying for*
 >
 >
 >>it was added to make the *intention* of the query clearer.
 >
 >
 > More clearer to whom?
 >
 > Certainly not developers who have been working for many years
 > using the old syntax.
 >
 > The intention of the old syntax is perfect. Realize that the problem
is
 > not the old syntax, the problem is the watered down database field
 > today.
 > I see this more and more with each interview I conduct looking
 > for dba's and developers.
 >

I generally agree with your assessment of the state of database=20
knowledge (particularly re developers).  It is, however, the reality we=20
live in.

[snipped nostalgia and back-patting]

 > I've never worked in a place that used ANSI only syntax and I've
never
 > had a problem with clarity nor any developers I've worked with.
 > So, I don't at all get what you're saying...

 > Old style is short and sweet and perfect.
 > Ansi dumbed it down, that's the bottom line.
 > And for people who've been developing for sometime,
 > It's wholly unnecessary.
 >

Well, perhaps you will one day and a developer will hose your server=20
with a "accidental" cross join and then you will understand.

But hopefully not.  ;)

 > Regards,
 >   Anthony
 >

[rest snipped]

--=20
Daryl
Director of Technology

((         Brandywine Asset Management          )
  ( "Expanding the Science of Global Investing"  )
  (          http://www.brandywine.com           ))