Geek Answers Handbook

SQL LEFT JOIN not working properly

I am having trouble getting the result from my query. I want to get the amount and total unit sale account in transactions where the transaction is in a specific zip code.

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 entries:

 +-----+----------------------+----------------+ | 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 | | ... | ... | ... | +-----+----------------------+----------------+

This is what 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

that leads to:

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

I was expecting another line with the WV construct, but it is not displayed. Anyone who knows where I'm wrong with this?

+4
source share
5 answers

I managed to fix my problem. I would like to share with you my result:

 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 tried using a subquery in my ON declaration, which gives my following results:

 +-----------+-----------+-------------------+------+ | SUM(..) | COUNT() | unit_name | id | +-----------+-----------+-------------------+------+ | NULL | 0 | WV construction | 1 | | 40014 | 11 | WV resale | 2 | | NULL | 0 | WV rent | 3 | +-----------+-----------+-------------------+------+

I would like to thank everyone who helped me resolve this issue.

+1
source

I think the correct connection is causing the problem.

try the following:

 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 WHERE ut.department_id = 1 GROUP BY unit_name ORDER BY ut.id

What is the result?

0
source

This may not solve the problem, but why the RIGHT JOIN property ?

LEFT JOIN property makes sense instead.

You see, the transaction table is already LEFT JOIN ed for unit_type , which I assume is the base table of this query.

0
source

This right link effectively cancels the LEFT JOIN point in front of it.

0
source

try the following:

 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 LEFT JOIN `property` p ON p.id = t.property_id WHERE ut.department_id = 1 AND p.property_zip_id = 1459 GROUP BY unit_name, p.property_zip_id -- added another column ORDER BY ut.id

UPDATE 1

 SELECT * FROM ( ( SELECT SUM(commission_fix_out) total_fix_out, COUNT(commission_fix_out) count_fix_out, unit_name, ut.id FROM unit_type as ut LEFT JOIN `transaction` as t ON ut.id = t.unit_type_id LEFT JOIN `property` p ON p.id = t.property_id WHERE ut.department_id = 1 AND p.property_zip_id = 1459 GROUP BY unit_name, p.property_zip_id -- added another column ORDER BY ut.id ) tableA UNION ( SELECT 0 as total_fix_out, 0 as count_fix_out, unit_name, id FROM unit_type WHERE id NOT IN ( SELECT DISTINCT xx.id FROM unit_type as xx LEFT JOIN `transaction` as t ON xx.id = t.unit_type_id LEFT JOIN `property` p ON p.id = t.property_id WHERE xx.department_id = 1 AND p.property_zip_id = 1459 ) ) tableA ) tableC
0
source

More articles:


All Articles