RE: [CF-Database] quick Q: outer join syntax

[Adrian Lynch <adrian.l@thoughtbubble.net>]

I may be reading this wrong, but what is the INNER JOIN joining on?

SELECT etc
FROM fundtrack t
INNER JOIN donor d -- NO ON CLAUSE HERE
LEFT OUTER JOIN donorgrp dg ON d.cmpy_code = dg.cmpy_code AND d.donor_grp =
dg.grp_code				
LEFT OUTER JOIN donorsts ds ON d.cmpy_code = ds.cmpy_code AND d.donor_status
= ds.status_code

Ade

-----Original Message-----
From: Barry L Beattie [mailto:blbt@email.com] 
Sent: 07 January 2005 04:40
To: database@list.cfdeveloper.co.uk
Subject: [CF-Database] quick Q: outer join syntax



hi all
I've got an informix 9.31 db* (accessed via jdbc) where I can't quite get
this outer join correct. the jdbc error messages are quite useless, except
it's a syntax problem.

rules: a "donor" may have 0 to many matching records in donorgrp or donorsts
(hence the left join)

the problem starts when I add a 2nd outer join to the donor table. Either
works fine by itself but dies if both are used

can anyone see where I've got it wrong?

thanx
barry.b

* the db is mostly ANSI SQL-99 compliant

===========================================================================
-- select etc
	from
		fundtrack t
		inner join donor d

-- there are 2 outer joins to the donor table

			left join donorgrp dg
			on d.cmpy_code = dg.cmpy_code
			and d.donor_grp = dg.grp_code


			left join donorsts ds
			on d.cmpy_code = ds.cmpy_code
			and d.donor_status = ds.status_code

-- either works fine by itself
-- but dies if both are used
		
		on t.cmpy_code = d.cmpy_code
		and t.donor_num = d.donor_num
		
		left join fundprobable p
		on t.cmpy_code = p.cmpy_code
		and t.prob_code = p.prob_code
-- etc
--