發表者討論內容

冷日
(冷日) 發表時間：2016/10/13 6:56

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15773

[轉貼]Double LEFT JOIN

Double LEFT JOIN

I want to receive property name and units count and specails count. I have this query:

SELECT
  `property`.`property_name`,
  COUNT(unit_id) AS `units_count`,
  COUNT(special_id) AS `specials_count`
FROM `property`
  LEFT JOIN `property_unit` ON unit_property_id = property_id
  LEFT JOIN `property_special` ON special_property_id = property_id
WHERE (property_id = '1')
GROUP BY `property_id`
ORDER BY `property_name` ASC

But it is not working properly. If I have one of these left joins - it's ok, but if I have two, I get this result:

["property_name"] => string(11) "Rivers Edge"
["units_count"] => string(1) "2"
["specials_count"] => string(1) "2"

Specials count is 2 and units_count is 2, but units count is really '1'. How can I get correct counts for it?
P.S: for those who know Zend Framework:

$select->setIntegrityCheck(FALSE)
    ->from(
        'property',
        array(
            'property_name',
        )
    )
    ->joinLeft(
        'property_unit',
        'unit_property_id = property_id',
        array(
            'units_count' => 'COUNT(unit_id)'
        )
    )
    ->joinLeft(
        'property_special',
        'special_property_id = property_id',
        array(
            'specials_count' => 'COUNT(special_id)'
        )
    )
    ->group('property_id')
    ->order('property_name');

--------------------------------------------------------------------------------

Try this:

SELECT
  `property`.`property_name`,
  COUNT(distinct unit_id) AS `units_count`,
  COUNT(distinct special_id) AS `specials_count`
FROM `property`
  LEFT JOIN `property_unit` ON unit_property_id = property_id
  LEFT JOIN `property_special` ON special_property_id = property_id
WHERE (property_id = '1')
GROUP BY `property_id`
ORDER BY `property_name` ASC

EDIT:
You shouldn't always use distinct - it happens to be the right option in this case.
select count(fieldname) returns the number of times that fieldname is not null; select count(distinct fieldname) returns the number of distinct values of fieldname.
In the original query, property_unit and property_special aren't joined to each other, only to property - so for a single property that had 5 units and 7 specials, 35 rows would be returned; therefore count(unit_id) and count(special_id) would both return 35. Since there would be 5 distinct values of unit_id and 7 distinct values of special_id (because these fields uniquely identify their records), count(distinct ...) returns the correct values in these circumstances.

--------------------------------------------------------------------------------

Your SQL should be something like this:

SELECT
  `property`.`property_name`,
  COUNT(property_unit.unit_id) AS `units_count`,
  COUNT(property_special.special_id) AS `specials_count`
FROM `property`
  LEFT JOIN `property_unit` ON (property_unit.unit_property_id = property.property_id)
  LEFT JOIN `property_special` ON (property_special.special_property_id = property.property_id)
WHERE (property.property_id = '1')
GROUP BY `property.property_id`
ORDER BY `property.property_name` ASC

原文出處：sql - Double LEFT JOIN - Stack Overflow

回覆

平面展開

前一個主題 | 下一個主題 | |

| |

討論串

[轉貼]USE MULTIPLE LEFT JOIN (冷日
(冷日), 2016/10/13 6:53)
» [轉貼]Double LEFT JOIN (冷日
(冷日), 2016/10/13 6:56)

發表新主題

DB研討會 : [轉貼]USE MULTIPLE LEFT JOIN

討論串