 |
|
茫茫網海中的冷日
發生過的事，不可能遺忘，只是想不起來而已！ |
|
|
恭喜您是本站第 1730377
位訪客!
登入 | 註冊
|
|
|
|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2016/10/13 6:53 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15773
|
- [轉貼]USE MULTIPLE LEFT JOIN
- USE MULTIPLE LEFT JOIN
Is it possible to use multiple left join in sql query? if not then whats the solution?
LEFT JOIN
ab
ON
ab.sht = cd.sht
i want to add to atach one more query like this to it? will it work?
LEFT JOIN
ab AND aa
ON
ab.sht = cd.sht
AND
aa.sht = cc.sht
Will this work?
--------------------------------------------------------------------------------
Yes it is possible. You need one ON for each join table.
LEFT JOIN ab
ON ab.sht = cd.sht
LEFT JOIN aa
ON aa.sht = cd.sht
Incidentally my personal formatting preference for complex SQL is described in http://bentilly.blogspot.com/2011/02/sql-formatting-style.html. If you're going to be writing a lot of this, it likely will help.
--------------------------------------------------------------------------------
Yes, but the syntax is different than what you have
SELECT
<fields>
FROM
<table1>
LEFT JOIN <table2>
ON <criteria for join>
AND <other criteria for join>
LEFT JOIN <table3>
ON <criteria for join>
AND <other criteria for join>
--------------------------------------------------------------------------------
The required SQL will be some like:-
SELECT * FROM cd
LEFT JOIN ab ON ab.sht = cd.sht
LEFT JOIN aa ON aa.sht = cd.sht
....
Hope it helps.
--------------------------------------------------------------------------------
You have two choices, depending on your table order
create table aa (sht int)
create table cc (sht int)
create table cd (sht int)
create table ab (sht int)
-- type 1
select * from cd
inner join cc on cd.sht = cc.sht
LEFT JOIN ab ON ab.sht = cd.sht
LEFT JOIN aa ON aa.sht = cc.sht
-- type 2
select * from cc
inner join cc on cd.sht = cc.sht
LEFT JOIN ab
LEFT JOIN aa
ON aa.sht = ab.sht
ON ab.sht = cd.sht
原文出處：sql - USE MULTIPLE LEFT JOIN - Stack Overflow
|
|
|
|
|
冷日
(冷日) |
發表時間：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
|
|
|
|
