SEO

#1 (**permalink**) 04-09-2008, 12:02 PM

I am having trouble getting joins to work. In a Java app that uses Hibernate 3.1, I am able to build queries that join two, three or more tables using combinations of INNER JOIN, LEFT JOIN or RIGHT JOIN. But, I need FULL OUTER JOIN to work and have not been able to get them to work in Hibernate.

So I decided to go back to basics and practice trial joins in the PgAdminIII Query tool (v1.4.1, Dec 05).

Just to warm up, I did the following simple queries which all worked:
select * FROM rack r
select *FROM sample s

The above two tables are linked. But, none of the following SQL worked:
select * FROM rack r JOIN sample s
select * FROM rack r INNER JOIN sample s

In each case I get a message "ERROR: syntax error at end of input at character X" where X is the last character inthe statement.

What am I doing wrong?

I have done tons of joins in Hibernate between the rack and sample tables, so you might take my word for it that the two tables mentioned are indeed linked.

TIA,
Bill

#2 (**permalink**) 04-09-2008, 12:02 PM

On Tue, Oct 24, 2006 at 02:43:07PM -0700, Bill Ewing wrote:
> I am having trouble getting joins to work. In a Java app that uses Hibernate 3.1, I am able to build queries that join two, three or more tables using combinations of INNER JOIN, LEFT JOIN or RIGHT JOIN. But, I need FULLOUTER JOIN to work and have not been able to get them to work in Hibernate.
>
> So I decided to go back to basics and practice trial joins in the PgAdminIII Query tool (v1.4.1, Dec 05).
>
> Just to warm up, I did the following simple queries which all worked:
> select * FROM rack r
> select * FROM sample s
>
> The above two tables are linked. But, none of the following SQL worked:
> select * FROM rack r JOIN sample s
> select * FROM rack r INNER JOIN sample s

These statements are incomplete. You need to say what you're joining
on. For example:

select * FROM rack r JOIN sample s USING (joinfield)

or
select * FROM rack r JOIN sample s ON (r.a = s.b);

If you really don't want any constraints, use a comma, or a cross join

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFPo3uIB7bNG8LQkwRAhtWAJ9Yrp3OjofjOSh0J+bV9z lwlLAt1gCffRsC
xIVHpc0lrNigt4ylatnPrr4=
=cN8R
-----END PGP SIGNATURE-----

#3 (**permalink**) 04-09-2008, 12:02 PM

On Oct 25, 2006, at 6:43 , Bill Ewing wrote:

> The above two tables are linked. But, none of the following SQL
> worked:
> select * FROM rack r JOIN sample s
> select * FROM rack r INNER JOIN sample s
>
>
> In each case I get a message "ERROR: syntax error at end of input
> at character X" where X is the last character in the statement.
>
> What am I doing wrong?

Unless you're using NATURAL JOIN, you need to specify the join
condition using a USING or ON clause, e.g.,

SELECT *
FROM rack r
JOIN sample s USING (rack_id)

or

SELECT *
FROM rack r
JOIN sample s ON (r.rack_id = s.rack_id)

That should do it.

Michael Glaesemann
grzm seespotcode net

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

#4 (**permalink**) 04-09-2008, 12:07 PM

Please note that natural joins may be dangerous in production code. See
the following thread for more detailed information...

http://forums.oracle.com/forums/thre...hreadID=440287

#5 (**permalink**) 04-09-2008, 12:08 PM

On Nov 4, 2006, at 5:44 , Anonymous wrote:

> Please note that natural joins may be dangerous in production code.
> See
> the following thread for more detailed information...
>
> http://forums.oracle.com/forums/thre...hreadID=440287

All that thread shows is that people are using natural join without
understanding what it means. The result is the same in *any*
language: using syntax that you don't understand will probably result
in unexpected results.

Michael Glaesemann
grzm seespotcode net

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to get joins to work