Обсуждение: adding missing FROM-clause
Dear All, I have a simple join query SELECT c1 FROM t1 INNER JOIN t2 ON t2.c2 = t1.c2 WHERE t3.c3= t2.c3; Which gives the expected result but I get the message NOTICE: adding missing FROM-clause entry for table "t3" How do I get rid of this NOTICE, i.e. how should I construct my select query. Many thanks Colin _________________________________________________________________ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Friday 29 October 2004 08:42 am, C G wrote: > Dear All, > > I have a simple join query > > SELECT c1 FROM t1 > INNER JOIN > t2 ON t2.c2 = t1.c2 WHERE t3.c3= t2.c3; ^^^^^ Your join doesn't alias anything as t3 I'd say you get rid of the notice once you replace t3 with t1 > > Which gives the expected result but I get the message > NOTICE: adding missing FROM-clause entry for table "t3" > > How do I get rid of this NOTICE, i.e. how should I construct my select > query. > > Many thanks > > Colin > > _________________________________________________________________ > Want to block unwanted pop-ups? Download the free MSN Toolbar now! > http://toolbar.msn.co.uk/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBguJDjqGXBvRToM4RArh0AJ46apSm5O/dX4QYJxuC44yyjYcWHQCfZpMu +UU1SoXolAFx7fEhMUUXp5w= =GRUr -----END PGP SIGNATURE-----
On Sat, 30 Oct 2004 01:42 am, C G wrote: > Dear All, > > I have a simple join query > > SELECT c1 FROM t1 > INNER JOIN > t2 ON t2.c2 = t1.c2 WHERE t3.c3= t2.c3; > Instead SELECT c1 FROM t2, t1 INNER JOIN t2 ON t2.c2 = t1.c2 WHERE t3.c3=t2.c3 OR SELECT c1 FROM t1 INNER JOIN t2 ON t2.c2 = t1.c2 JOIN t3 ON t3.c3 = t2.c3 > Which gives the expected result but I get the message > NOTICE: adding missing FROM-clause entry for table "t3" > > How do I get rid of this NOTICE, i.e. how should I construct my select > query. > > Many thanks > > Colin > > _________________________________________________________________ > Want to block unwanted pop-ups? Download the free MSN Toolbar now! > http://toolbar.msn.co.uk/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > >
csgcsg39@hotmail.com ("C G") writes: > Dear All, > > I have a simple join query > > SELECT c1 FROM t1 > INNER JOIN > t2 ON t2.c2 = t1.c2 WHERE t3.c3= t2.c3; > > Which gives the expected result but I get the message > NOTICE: adding missing FROM-clause entry for table "t3" > > How do I get rid of this NOTICE, i.e. how should I construct my select > query. > SELECT c1 FROM t1, t2, t3 WHERE t2.c2 = t1.c2 AND t3.c3 = t2.c3; or SELECT c1 FROM t1 INNER JOIN t2 ON t2.c2 = t1.c2 INNER JOIN t3 ON T3.c3 = t2.c3; The above can also be written as SELECT c1 FROM t1 JOIN t2 USING(c2) JOIN t3 USING(c3); or even SELECT c1 FROM t1 NATURAL JOIN t2 NATURAL JOIN t3; This last might be problematic if t3 has a column named c1. Question: Is there any advantage to specifying USING() rather than ON? I know that if I do SELECT * from T1 JOIN t2 USING(col) then I only get 1 instance of col in the returned rows, but I'm wondering if there is any advantage to the planner by specifying either USING() or ON? -- Remove -42 for email