Wednesday 5 September 2018

MySQL LEFT JOIN does not work correctly

I'm having trouble getting the result out of my query. I want to fetch the sum and total count of an unit sale in transactions, where the transaction is in a specific zip.

Here are my tables:
TABLE unit_type(
    id (Primary key)
    unit_name (varchar)
    department_id (Foreign key)
)

TABLE transaction(
    id (PK)
    commission_fix_out (int)
    transaction_end_week (int)
    property_id (FK)
    unit_type_id (FK)
    ...
)

TABLE property(
    id (PK)
    property_zip_id (FK)
    ...
 )

My unit_types table has the following records:
+-----+----------------------+----------------+
| id  | unit_name            | department_id  |
+-----+----------------------+----------------+
| 1   | WV construction      | 1              |
| 2   | WV resale            | 1              |
| 3   | WV rent              | 1              |
| 4   | BV industrial sale   | 2              |
| 5   | BV industrial rent   | 2              |
| ... | ...                  | ...            |
+-----+----------------------+----------------+

Here's how my query looks like:
SELECT SUM(commission_fix_out), COUNT(commission_fix_out), unit_name, ut.id
FROM unit_type as ut
LEFT JOIN transaction as t
ON ut.id = t.unit_type_id
RIGHT JOIN property as p
ON (p.id = t.property_id AND p.property_zip_id = 1459)
WHERE ut.department_id = 1
GROUP BY unit_name
ORDER BY ut.id

which results in:
+------------+-------------+-------------+---------+
| SUM(...)   | COUNT(..)   | unit_name   | ut.id   |
+------------+-------------+-------------+---------+
| 40014      | 11          | WV resale   | 2       |
| NULL       | 0           | WV rent     | 3       |
+------------+-------------+-------------+---------+

I was expecting another row with WV construction, but it doesn't show up. Anyone who knows where i am wrong with this one?

I managed to fix my problem. I'd like to share my result with you:
SELECT SUM(commission_fix_out), COUNT(commission_fix_out), unit_name
FROM unit_type ut
LEFT JOIN transaction t
ON (ut.id = t.unit_type_id AND t.property_id IN (SELECT id FROM property p WHERE
property_zip_id = 1459))
WHERE department_id = 1
GROUP BY unit_name
ORDER BY ut.id

Instead of using an extra JOIN, i'd tried using a subquery in my ON-clause which gives my next results:
+-----------+-----------+-------------------+------+
| SUM(..)   | COUNT()   | unit_name         | id   |
+-----------+-----------+-------------------+------+
| NULL      | 0         | WV construction   | 1    |
| 40014     | 11        | WV resale         | 2    |
| NULL      | 0         | WV rent           | 3    |
+-----------+-----------+-------------------+------+

0 comments:

Post a Comment