The main problem is the 2nd join condition - ON incomes.cust_id. That is joining every row that has a true incomes.cust_id with every row in the customer table, i.e. incomes.cust_id 1 with customer ids 1,2,3,…, incomes.cust_id 2 with customer ids 1,2,3,…, income.cust_id 3 with customer ids 1,2,3,…,… It is missing a relationship for the rows in the customer table.
Some suggestions -
- Use table alias names to simplify the syntax, something like i for income, t for type, c for customer.
- Specifically list the table_alias.column names you are SELECTing. This helps to write queries that retrieve only the data you want, and helps to document what you are doing.
- Use LEFT JOINs when you want all the data from the left-hand side of the query regardless of any matching data on the right-hand side.
So, what exactly are you doing - selecting customer information and any income/income type information. You would have a LEFT JOIN between the customer table and the incomes table and income type table, since there may not be any incomes for a customer. Your query would end up looking like -
SELECT c.some_customer_column, ..., i.some_incomes_column, ..., t.some_inctype_column, ...
FROM customer c
LEFT JOIN incomes i ON c.cust_id = i.cust_id
LEFT JOIN inctype t ON i.inctype_id = t.inctype_id
WHERE c.cust_id = ?
ORDER BY i.inc_date DESC
Note: you should use a prepared query when supplying unknown data to an sql query statement, hence the ? place-holder in the above. Also, the table/alias isn’t required for uniquely named columns (the inc_date), but if you always include it, anyone (like help forum members) can read your query and know what columns are from which table without having to know or guess what your naming scheme is.