Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Join six tables with single SELECT statement
Message
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
01316996
Message ID:
01317204
Views:
10
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--
Previous
Reply
Map
View

Click here to load this message in the networking platform