Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Join six tables with single SELECT statement
Message
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
01316996
Message ID:
01317204
Vues:
11
In this case you definitely cannot keep conditions in the WHEE clause because they will apply after JOIN and effectively kill OUTER JOIN
try
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 visual v ;
	FULL JOIN liquid l on l.custid = v.custid and l.upsino = v.upsino and v.datinp = l.puldat ;
		AND v.custid = "SC100234" and v.upsino = "0000004" ;
	   	AND l.custid = "SC100234" and l.upsino = "0000004" ;
	FULL JOIN dga d on d.custid = l.custid and d.upsino = l.upsino and d.puldat = l.puldat ;
	   	AND d.custid = "SC100234" and d.upsino = "0000004" ;
	FULL JOIN icp i on i.custid = d.custid and i.upsino = d.upsino and i.puldat = d.puldat ;
	   	AND i.custid = "SC100234" and i.upsino = "0000004" ;
	FULL JOIN furanppm f on f.custid = d.custid and f.upsino = d.upsino and f.puldat = d.puldat ;
	   AND f.custid = "SC100234" and f.upsino = "0000004" ;
	order by v.upsino, v.datinp
>I understand that the LEFT JOIN is including all records from VISUAL that meet the criteria and only those records in the other tables that meet the join conditions. Here's my issue though, I may have records in the other tables that have a PULDAT that is unique to that table. For example, I have a single test result in the FURANPPM table with a PULDAT of 10/28/2002. This is the only record with that puldat. It doesn't exist in any other tables including the visual, but I want it included in my results.
>
>How do I ensure I get ALL records regardless of PULDAT and still match-up those records that have same PULDATs. Do I need to add several OR clauses to my WHERE clause as in ...
>
>
>   WHERE v.custid = "SC100234" and v.upsino = "0000004" ;
>   OR l.custid = "SC100234" and l.upsino = "0000004" ;
>   OR d.custid = "SC100234" and l.upsino = "0000004" ;
>   OR i.custid = "SC100234" and l.upsino = "0000004" ;
>   OR f.custid = "SC100234" and l.upsino = "0000004" ;
>
>
>
--sb--
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform