Thread: how to do 'deep queries'?
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 /> 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/><br /><br />
> 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 = bar.id > and > bar.baz_id = baz.id This works for me.. SELECT table1.state, table2.coursename, table3.firstname FROM backend.enrolments table1, backend.courses table2, backend.users table3WHERE table1.user = table3.employeeno AND table1.course = 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/
<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>
Nevermind. It's late here and I'm not thinking clearly. Problem solved.<br />
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
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. 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. You know, it used to be that database developers had a solid background in math and relational theory. Sadly, that's not the case anymore... select * from a,b where a.id=b.id Suggests a Cartesian product between two relations then a filter to keep only matching rows. 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 > 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. 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. > > 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
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 ))
> 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 ))
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 payingfor*>>>>it was added to make the *intention* of the query clearer.>>> More clearer to whom?>> Certainly not developerswho have been working for many years> using the old syntax.>> The intention of the old syntax is perfect. Realizethat the problem is> not the old syntax, the problem is the watered down database field> today.> I see this more andmore 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'veworked 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 developingfor 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 ))
> Well, perhaps you will one day and a developer will hose your server > with a "accidental" cross join and then you will understand. Hehe :)) 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] 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 windowfunctions 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 payingfor*>>>>it was added to make the *intention* of the query clearer.>>> More clearer to whom?>> Certainly not developerswho have been working for many years> using the old syntax.>> The intention of the old syntax is perfect. Realizethat the problem is> not the old syntax, the problem is the watered down database field> today.> I see this more and more with each interviewI 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 developingfor 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 ))