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??