adding missing FROM-clause

[C G]

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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Edmund Bacon]

Re: adding missing FROM-clause

csgcsg39@hotmai l.com ("C G") writes:
[color=blue]
> 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.
>[/color]

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

[Uwe C. Schroeder]

Re: adding missing FROM-clause

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 29 October 2004 08:42 am, C G wrote:[color=blue]
> 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;[/color]
^^^^^
Your join doesn't alias anything as t3
I'd say you get rid of the notice once you replace t3 with t1
[color=blue]
>
> 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[/color]

- --
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)

iD8DBQFBguJDjqG XBvRToM4RArh0AJ 46apSm5O/dX4QYJxuC44yyjY cWHQCfZpMu
+UU1SoXolAFx7fE hMUUXp5w=
=GRUr
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Russell Smith]

Re: adding missing FROM-clause

On Sat, 30 Oct 2004 01:42 am, C G wrote:[color=blue]
> 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;
>[/color]
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
[color=blue]
> 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
>
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match