CODEWITHSUNDEEP
mysql
VeeBee
VeeBee

Reputation: 847

MySQL: joining on aliased tables

I am trying to join users_groups from my main query but i can't seem to join them on the aliased tables. Please help me. It results to an error: Unknown column 'node.id' in 'on clause'

SELECT node.id, node.first_name, node.last_name, (COUNT( parent.id ) - ( sub_tree.depth +1 ) ) AS depth
FROM users AS node, users AS parent, users AS sub_parent, (
    SELECT node.id, node.first_name, node.last_name, (COUNT( parent.id ) -1) AS depth
    FROM users AS node, users AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.id =1
    GROUP BY node.id
    ORDER BY node.lft
) AS sub_tree
JOIN users_groups ON users_groups.user_id = node.id
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.id = sub_tree.id
GROUP BY node.id
ORDER BY node.lft
LIMIT 0 , 30

Upvotes: 0

Views: 91

Answers (2)

Akash
Akash

Reputation: 5012

Formatting your SQL is always helpful

SELECT 
  node.id, 
  node.first_name, 
  node.last_name, 
  ( COUNT( parent.id ) - ( sub_tree.depth +1 ) ) AS depth
FROM 
  users AS node, 
  users AS parent, 
  users AS sub_parent, 
  (
    SELECT
      node.id, 
      node.first_name, 
      node.last_name, 
      ( COUNT( parent.id ) -1 ) AS depth
    FROM 
      users AS node, 
      users AS parent
    WHERE 
      node.lft BETWEEN parent.lft AND parent.rgt
        AND node.id =1
    GROUP BY 
      node.id
    ORDER BY 
      node.lft
  ) AS sub_tree
  JOIN users_groups ON users_groups.user_id = node.id
                                              ^^ should be sub_tree.id
WHERE 
  node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.id = sub_tree.id
GROUP BY 
  node.id
ORDER BY 
  node.lft
LIMIT 0 , 30

Upvotes: 3

banging
banging

Reputation: 2550

You must use the alias you gave it..

JOIN users_groups ON users_groups.user_id = sub_tree.id

Upvotes: 2

Related Questions