Thread: How to get joins to work

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
jointwo, three or more tables using combinations of INNER JOIN,  LEFT JOIN or RIGHT JOIN.  But, I need FULL OUTER JOIN
towork 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.

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

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

http://forums.oracle.com/forums/thread.jspa?threadID=440287

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/thread.jspa?threadID=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