Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
JOIN vs WHERE
Message
De
16/06/2009 13:59:49
 
 
À
Tous
Information générale
Forum:
Visual FoxPro
Catégorie:
Codage, syntaxe et commandes
Titre:
JOIN vs WHERE
Versions des environnements
Visual FoxPro:
VFP 8 SP1
Divers
Thread ID:
01406428
Message ID:
01406428
Vues:
140
Please Note: VFP 8

I have a cursor (crsAllCust) with a list of customer names. There are 30 records in this table.
I have another cursor (crsFillRate) with some customer data in it.

I need to join these tables to provide a count of how many records there are per customer which meet a certain criterion. I need a record returned even if there are none with a matching condition. There are three customers which have no data in crsFillRate. There are an additional two customers who have records, but without any data matching the condition.
SELECT  cust.customer, COUNT(*) ;
   FROM crsAllCust cust;
      LEFT JOIN crsFillRate fr ON cust.customer = fr.custshipm;
   WHERE EMPTY(fr.PartOrders);
   GROUP BY cust.customer
The five "nodata" records fall out of the result. By changing the query to
SELECT  cust.customer, COUNT(*) ;
   FROM crsAllCust cust;
      LEFT JOIN crsFillRate fr ON cust.customer = fr.custshipm AND EMPTY(fr.PartOrders);
   GROUP BY cust.customer
I get all the customers.

I think I've had this explained to me before, but apparantly it hasn't stuck (sorry Tamar). Could somebody please give me a refresher course of what the query steps are that cause the five records to fall out of the first query?

Thanks..........Rich
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform