WHERE clause

Question

Hello Experts

I guess i am reaching out to Naomi on this one

This condition 

WHERE cst.name = lcfullname ;

converts LEFT JOIN into INNER JOIN. Please see this link that explains the problem in details

originally I had my select statement like this

SELECT hdr.invoiceid, cst.name, ;
hdr.date, hdr.licenseno, hdr.vin, hdr.makemodel, hdr.total FROM invoicehdr hdr ;
left JOIN customers cst ON cst.customerid = hdr.customerid ;
order by hdr.date where cst.name = lcfullname ; 
into cursor curExport

I guess what i am trying to understand is if i am really understanding Noami's point

where i changed my select statement to 

SELECT hdr.invoiceid, cst.name, ;
hdr.date, hdr.licenseno, hdr.vin, hdr.makemodel, hdr.total FROM invoicehdr hdr ;
left JOIN customers cst ON cst.customerid = hdr.customerid ;
AND cst.name = lcfullname ;
ORDER BY hdr.date ;
into cursor curExport

If I do this, then the result is also wrong as i am searching for only the lcfullname specified   example: Megan Fox
I got deleted items that had no name? blank?
I suppose I could just "set deleted on" and that should resolve the problem, but the result is still wrong?

please correct me if im wrong or please explain

Answer 1

Putting the condition from WHERE to the join ON condition is correct. I doubt you look into the right browse, if you see wrong names in the result. If you really set lcfullname to "Megan Fox", you only will get Megan Fox or empty (NULL) names, when there is no match. And if you don't want these empty name results you WANT an INNER join and should simply revert to the WHERE clause.

Add a Messagebox(lcfullname) before the SQL and see, if you really look for what you think you look for.

Also, try to find out, if an earlier suspicion is true after all: You may have orphaned data, that is invoicehdr records with a non valid customerid.

See if this turns out any orphaned invoicehdr records:

SELECT * From invoicehdr Where invoicehdr.customerid NOT in (Select customerid From customers)

You have to address all these invoices as either they need to be deleted or you need to find the customer you wanted to bill. All these records will come into the result, if you do a LEFT join of customers, as a LEFT join means you want ALL invoicehdr records, no matter if they point to a customer or not. All these invoicehdr have no customer. And filtering in the join condition only filters the found matches, if there is no match found by customerid alone, then there is no match by customerid and customer name, too. Adding the condition to the join clause filters out invoices for other customers, but not the invoices with no valid customer related to them. So if you see too few invoices with the condition in the WHERE clause and too many with the condition in the JOIN clause, that's easily explained. Some of the orphaned invoices are for Megan Fox, others are not. All that means is, you will need to take care for the list of orphaned invoices and either delete them or assign them to the correct customer by any other data in the invoice letting you know the customer the invoice was for.

No code change of the SQL helps you here, sort out your data.

Bye, Olaf.

Answer 2

INNER JOIN simply solved the problem. Thank you olaf