>I have a parent table (Customer.DBF) and child table (Invoice.dbf). I want to list all customers with invoice information. Using a Select command, what is the best way to list ALL customers, whether they have matching invoices or not.
>
>For instance, the following will ONLY return records that find a matching invoice:
>
>Select * ;
> from customer, invoice ;
> where invoice.cust_id = customer.cust_id ;
> into cursor temp
>
>What is the best way to handle this, preferably in a single SELECT command?
>
>Thanks,
>
>David
The simplest way is to use the new JOIN syntax (VFP 5 or later).
Your above query is equivalent to the following:
Select * ;
from customer join invoice on Customer.Cust_Id = Invoice.Cust_Id;
into cursor temp
To get all customers, add the LEFT clause:
Select * ;
from customer LEFT join invoice on Customer.Cust_Id = Invoice.Cust_Id;
into cursor temp
For customers without invoices, you will get NULL values for the corresponding fields in invoices.
For a quick start, use the query designer (CREATE QUERY), which creates this syntax automatically.
HTH,
Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)