Re: how to do 'deep queries'? - Mailing list pgsql-sql

From Anthony Molinaro
Subject Re: how to do 'deep queries'?
Date
Msg-id 3C6C2B281FD3E74C9F7C9D5B1EDA4582182570@wgexch01.wgenhq.net
Whole thread Raw
In response to how to do 'deep queries'?  (jeff sacksteder <jsacksteder@gmail.com>)
Responses Re: how to do 'deep queries'?
Re: how to do 'deep queries'?
List pgsql-sql
<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial;color:navy">that query is 100% correct.</span></font><p class="MsoNormal"><font color="navy"
face="Arial"size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">it’s just an equijoin (a type of inner join) between 3 tables.</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">the syntax you show is how queries should be written and is more</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy">representative of what a joins between relations really are:</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy">Cartesian products with filters applied</span></font><p class="MsoNormal"><font
color="navy"face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">the ansi syntax, the explicit JOIN … ON<span style="mso-spacerun:yes"> 
</span>stuffis (imho) unnecessary,</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">useful only for outer joins since all the vendors did it
differently.</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="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</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy">for every single join recipe except for outjoins.</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">are you seeing errors?</span></font><p class="MsoNormal"><font color="navy"
face="Arial"size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">regards,</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Arial;color:navy"><span style="mso-spacerun:yes">  </span>Anthony</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma">-----Original
Message-----<br/><b><span style="font-weight:bold">From:</span></b> pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]<b><span style="font-weight:bold">On Behalf Of </span></b>jeff sacksteder<br
/><b><spanstyle="font-weight:bold">Sent:</span></b> </span></font><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma">Monday,September 26, 2005</span></font><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma"></span></font><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma">8:34PM</span></font><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma"><br/><b><span style="font-weight:bold">To:</span></b>
pgsql-sql@postgresql.org<br/><b><span style="font-weight:bold">Subject:</span></b> [SQL] how to do 'deep
queries'?</span></font><pclass="MsoNormal" style="margin-left:.5in"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt"> </span></font><pclass="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
12.0pt;margin-left:.5in"><font face="Times New Roman" size="3"><span style="font-size:12.0pt">Is there supported syntax
todo 'deep' queries? That is where A relates to B relates to C, returning fields from each table?<br /><br /> This
doesn'tseem to work. Is there a google-able term for this sort of query?<br /><br /> select <br />    foo.aaa,<br />   
bar.bbb,<br/>    baz.ccc<br /><br /> from<br />    foo,bar,baz<br /><br /> where <br />    foo.bar_id = <a
href="http://bar.id">bar.id</a><br/> and<br />    bar.baz_id = <a href="http://baz.id">baz.id</a><br /><br
style="mso-special-character:line-break"/><br style="mso-special-character:line-break" /></span></font></div>   

pgsql-sql by date:

Previous
From: "Stewart Ben (RBAU/EQS4) *"
Date:
Subject: Re: how to do 'deep queries'?
Next
From: Tom Lane
Date:
Subject: Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT