SQL query syntax

[GTGeek88]

Can VFP handle this: I need to query to print an invoice. I need data from the Invoice Header table, the Invoice Details table (the line items on the invoice), and the Inventory table (some details related to the line item part numbers). Left joining the Header table (parent) to the Details table (child) is easy. But using left or right join from the Details table (child) to the Inventory table (grandchild) results in no records. I need a single record with the Header data (invoice number, invoice date, etc). And I need to NVL the fields from the Details table and the Inventory table just to avoid nulls. So far what I get is no records because of the join between the child and grandchild (the child does not exist - no line items on the invoice). This is causing no records to be returned. Is there a syntax within VFP's built-in SQL code to handle this (these are VFP tables). I can kludge a solution but was wanting a SQL query that works when there are detail (child) records and when there aren't.

 

[Chriss Miller]

If you do left joins you get NULLS for non existing children/grandchildren. Getting no records points towards not doing a left join but an iner join. Which also happens indirectly, when you put a join condition into where clauses instead of an actual join.

It would help to see your query to point out what to change.

 

[ggreen61]

I use the following code to do a similar select. I use NVL() function when the values are to be displayed.

SELECT inv.invoicedate, inv.duedate, inv.billto, inv.stataxes, inv.loctaxes, inv.cittaxes, inv.shipping, inv.totaldue, inv.amtpaid, inv.memberid, mbr.membername, ;
       itm.price, itm.discount, itm.quantity, itm.returnitem, itm.materialid, itm.salesordritmid, mat.descriptn, mat.itemnum ;
    FROM soinvhdr AS inv ;
    JOIN soinvitems AS itm ON itm.invoiceid = inv.id ;
    JOIN members AS mbr ON mbr.id = inv.memberid AND mbr.companyid = inv.companyid ;
    JOIN materials AS mat ON mat.id = itm.materialid AND mat.companyid = itm.companyid ;
    WHERE itm.companyid = _VFP.CompanyId ;
        AND inv.id = tcInvoiceId ;
    INTO CURSOR c_soinvitemsprn

 

[GTGeek88]

If you do left joins you get NULLS for non existing children/grandchildren. Getting no records points towards not doing a left join but an iner join. Which also happens indirectly, when you put a join condition into where clauses instead of an actual join.

It would help to see your query to point out what to change.

I have the left joins in there. There is no doubt about that. I don't see behavior that works when it's Parent --> Child --> Grandchild even when left joining Parent --> Child and left joining Child --> Grandchild. When it's only Parent --> Child, then it's easy.

 

[GTGeek88]

I use the following code to do a similar select. I use NVL() function when the values are to be displayed.

SELECT inv.invoicedate, inv.duedate, inv.billto, inv.stataxes, inv.loctaxes, inv.cittaxes, inv.shipping, inv.totaldue, inv.amtpaid, inv.memberid, mbr.membername, ;
       itm.price, itm.discount, itm.quantity, itm.returnitem, itm.materialid, itm.salesordritmid, mat.descriptn, mat.itemnum ;
    FROM soinvhdr AS inv ;
    JOIN soinvitems AS itm ON itm.invoiceid = inv.id ;
    JOIN members AS mbr ON mbr.id = inv.memberid AND mbr.companyid = inv.companyid ;
    JOIN materials AS mat ON mat.id = itm.materialid AND mat.companyid = itm.companyid ;
    WHERE itm.companyid = _VFP.CompanyId ;
        AND inv.id = tcInvoiceId ;
    INTO CURSOR c_soinvitemsprn

Ok, but in that example you're not left joining, which would be required (or right joining, depending) and you're not using NVL. So that works in my standard case where the invoice has line items, but not in the outlier where there are no invoice line items.

 

[igorsandler]

Can VFP handle this: I need to query to print an invoice. I need data from the Invoice Header table, the Invoice Details table (the line items on the invoice), and the Inventory table (some details related to the line item part numbers). Left joining the Header table (parent) to the Details table (child) is easy. But using left or right join from the Details table (child) to the Inventory table (grandchild) results in no records. I need a single record with the Header data (invoice number, invoice date, etc). And I need to NVL the fields from the Details table and the Inventory table just to avoid nulls. So far what I get is no records because of the join between the child and grandchild (the child does not exist - no line items on the invoice). This is causing no records to be returned. Is there a syntax within VFP's built-in SQL code to handle this (these are VFP tables). I can kludge a solution but was wanting a SQL query that works when there are detail (child) records and when there aren't.

I am confused by your explanation. If you need a single record from the parent, why would you need any detail in the first place? The only thing you would have from detail or child is some kind of amount if you group and the rest of information from the child will collapse to the last details(child) record but invoice header should have a full amount distributed between detail lines. Maybe I am missing something but, like other comments said, if you have header record=no details or child then there is no records from grandchild table

 

[GTGeek88]

I'm confused by your response, but the simple answer is that I'm printing a blank invoice and the invoice expects something in the fields for line item details. That data can't just not exist. The other simple answer is that I got it working as I'd originally expected.

 

[GTGeek88]

If you do left joins you get NULLS for non existing children/grandchildren. Getting no records points towards not doing a left join but an iner join. Which also happens indirectly, when you put a join condition into where clauses instead of an actual join.

It would help to see your query to point out what to change.

Well, I lied. I got some tunnel vision going. The left joins were in there for the main relationship (invoice header to line items to parts), but there was one other relation to bring in a bit of other, minor you might say, info and I wasn't spotting that as needing a left join.