SELECT
`r`.*,
GROUP_CONCAT(DISTINCT (f.id)) AS `faultnotice`,
`i`.`name` AS `investment_name`,
`i`.`stage` AS `investment_stage`,
`rs`.`name` AS `status_name`,
`rt`.`name` AS `type_name`,
GROUP_CONCAT(DISTINCT (sto.order_id)) AS `orders`,
`sto`.`subject_status_id` AS `subject_status`,
`cso`.`name` AS `contact_source_of_origin`,
(SELECT GROUP_CONCAT(realestate.building, " - ", realestate.name)
FROM realestate
# jeżeli usuniemy AND realestate.id != r.id zapytanie wykonuje się poprawnie
INNER JOIN transaction ON transaction.realestate_id = realestate.id AND realestate.id != r.id
WHERE transaction.id = (SELECT transaction_to_realestate.id
FROM transaction_to_realestate
#tutaj do r.id nie ma zastrzeżeń oO
WHERE transaction_to_realestate.contiguity_id = r.id) AND
realestate.realestate_type_id NOT IN (2, 4, 3, 5, 6, 7)) AS related_realestate
FROM `realestate` AS `r` LEFT JOIN `faultnotice` AS `f` ON f.realestate_id = r.id
LEFT JOIN `investment` AS `i` ON i.id = r.investment_id
LEFT JOIN `realestate_status` AS `rs` ON rs.id = r.realestate_status_id
LEFT JOIN `realestate_type` AS `rt` ON rt.id = r.realestate_type_id
LEFT JOIN `subject_to_order` AS `sto` ON sto.subject_id = r.id AND sto.subject_type = "realestate" AND sto.deleted = 0
LEFT JOIN `order` AS `o` ON o.id = sto.order_id
LEFT JOIN `contact` AS `c` ON c.contact_id = o.contact_id
LEFT JOIN `contact_to_phone` AS `ctp` ON ctp.contact_id = c.contact_id AND ctp.type = 0
LEFT JOIN `contact_source_of_origin` AS `cso` ON cso.id = c.source_of_origin_id
WHERE (r.deleted != 1)
W momencie, w którym dopiszę do INNER JOIN TRANSACTION ON TRANSACTION.realestate_id = realestate.id
AND realestate.id != r.id
otrzymuję error Unknown column 'r.id' in 'on clause'
. Natomiast ten sam r.id
wykorzystywany wewnątrz WHERE transaction_to_realestate.contiguity_id = r.id
smiga oO Jeżeli dodam do r.*
na samej górze @rid := r.id rid
i później odwołuję się do tego za pomocą @rid
również wszystko działa jak trzeba. Jest na to jakieś wyjaśnienie??