Hello List I am running a query to find accounts that not represented in an invoice:
Table structure is as follows: Invoice ---------------------- invoice_line_number account_number Account_Parameters ----------------------------- account_id parameter_id parameter_value Parameter_Library --------------------------- parameter_id parameter_description The account parameters table stores a number of parameters for each account entry including the account number used in the invoice. Therefore to match account_id to account_number I have to use the relevant parameter_value to match the account_number. I also want the query to return 0 if I found an account number in the invoice that I do not have an entry for in account_parameters. I use the invoice_line_number field to limit the number of records I pull from Invoice The query I am trying is as follows: SELECT IFNULL(AP.account_id, 0), I.account_number FROM Invoice I LEFT JOIN Account_Parameters AP ON AP.parameter_value = I.account_number JOIN Parameter_Library PL ON AP.parameter_id = PL.parameter_id AND PL.parameter_description = "Account_Number" WHERE I.invoice_line_number > 10 However, all that is returned is those records that match and not those from Invoice that didn't match. Meaning that somehow my LEFT JOIN is incorrect - can anybody help? Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
