>All
>
>I want to join various fields from six tables with one SELECT statement. Can that be done?
>
>I need it to be a full join because I need all records from all tables that match my criteria. I have started a SELECT statement with five of the six tables as shown below. I think I need to use the FORCE keyword here to control the join evauluation, do I not?
>
>In the results I am looking at now a record in the "furanppm" table is being excluded that should be there. I have checked this record and I can't find a record in any of the other tables that match its date. Could that be why it is not included? I thought the FULL JOIN would handld that and that means to bring it into the result set regardless.
>
>Here's my current work:
>
>

>select v.custid, v.upsino, v.datinp, v.optemp, v.pktemp, ;
>	   l.dielectric, l.neut_no, l.ift, l.h20, ;
>	   d.hydrogen, d.methane, d.ethylene, ;
>	   i.aluminum, i.copper, i.iron, ;
>	   f.hydroxymth, f.furfuryl, f.furaldehyd ;
>	   from force visual v ;
>	   full join (liquid l full join dga d on d.custid = l.custid and d.upsino = l.upsino and d.puldat = l.puldat ;
>	   full join icp i on i.custid = d.custid and i.upsino = d.upsino and i.puldat = d.puldat ;
>	   full join furanppm f on f.custid = d.custid and f.upsino = d.upsino and f.puldat = d.puldat) on ;
>	   l.custid = v.custid and l.upsino = v.upsino and v.datinp = l.puldat ;
>	   where v.custid = "SC100234" and v.upsino = "0000004" ;
>	   order by v.upsino, v.datinp
>

You do _not_ need the FORCE clause here. That's about optimization, not about what the joins do.

As Sergey said, you need to move the stuff that's in the WHERE clause into the JOIN. Otherwise, after you do the full join, all records that don't match the WHERE clause are filtered out. That includes all those created by the outer join with nulls for thc custid and upsino fields.

Tamar