| Rory McKinley 2005-12-28, 7:23 am |
| 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_descriptio
n
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_descriptio
n = "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/mysql? unsub...sie.nctu.edu.tw
|