CODEWITHSUNDEEP
mysqlsql
Petty_Crim
Petty_Crim

Reputation: 441

Left Join - Unknown Column Error

I have been recently going through my database code trying to improve on my old code with new techniques to make it more efficient. Recently I have been looking to JOINs and I spotted what I thought was a perfect opportunity to test it out.

I am currently using the following two queries to form a list of groups for a user:

SELECT g.group_id, g. name 
FROM assigned_groups ag, user_groups g 
WHERE ag.user_id=:user_id AND ag.group_id=g.group_id

SELECT g.group_id, g. name 
FROM users u, user_groups g 
WHERE u.user_id=:user_id AND u.base_group=g.group_id

Those two select statements give me the list but I would like to join them into one. The only catch is a user may or may not have any groups listed in "assigned_groups". Ie thats optional.

From what I understand that means I need a LEFT or RIGHT join. I have currently the following syntax:

SELECT g.group_id, g. name 
FROM 
  user_groups g, users u 
  LEFT JOIN assigned_groups ag ON ag.user_id=:user_id AND ag.group_id=g.group_id 
WHERE 
 u.base_group=g.group_id AND u.user_id=:user_id

However this is giving me the following error: Unknown column 'g.group_id' in 'on clause'

Here is an image of my table structure: enter image description here

Upvotes: 2

Views: 2154

Answers (5)

Emilio Llamas
Emilio Llamas

Reputation: 11

Try with parentesis on every pair of tables.

Example on your query:

SELECT g.group_id, g. name 
FROM 
  (user_groups g, users u )
  LEFT JOIN assigned_groups ag ON ag.user_id=:user_id AND ag.group_id=g.group_id 
WHERE 
 u.base_group=g.group_id AND u.user_id=:user_id

More general example:

SELECT a.*, b.*, c.*, d.*
FROM 
  (
     (
       (table1 a, table2 b)
       , table3 c
     )
     LEFT JOIN table4 d ON a.id=d.id and b.idx = d.idx
  )
WHERE 
 u.base_group=g.group_id AND u.user_id=:user_id

Upvotes: 1

Sergey Benner
Sergey Benner

Reputation: 4431

Perhaps this one will help you. The ag.name may have NULL when it returns rows as you have probably understood that. If you need that then exclude g.name field from the select.

SELECT u.name,ug.name
FROM  users u 
INNER JOIN assigned_groups ag 
ON u.user_id=ag.user_id
INNER JOIN user_groups ug
ON ag.group_id=ug.group_id    
WHERE u.user_id=:user_id

Cheers

Upvotes: 0

gbn
gbn

Reputation: 432271

Change from implicit to explicit JOIN.

This makes the g alias available later on in the FROM clause.

SELECT g.group_id, g.name, 
    ag.name -- guessing here
FROM 
  users u
  JOIN
  user_groups g ON u.base_group = g.group_id 
  LEFT JOIN 
  assigned_groups ag ON ag.user_id = :user_id AND ag.group_id = g.group_id 
WHERE 
  u.user_id = :user_id

Note: this also removes ambiguity in "which join to do first"

However, I think you really want this. It depends on the relationship between u and ag

Edit 2, after more comments

SELECT g.group_id, g.name , 1 as IsBaseGroup
FROM 
  users u
  JOIN
  user_groups g ON u.base_group = g.group_id 
WHERE 
  u.user_id = :user_id
UNION
SELECT ag.group_id, ag.name, 0 as IsBaseGroup
FROM 
  users u
  JOIN 
  assigned_groups ag ON u.user_id = ag.user_id
  JOIN
  user_groups g ON ag.group_id  = g.group_id 
WHERE 
  u.user_id = :user_id;

Edit, after comment.

SELECT 
   g.group_id, g.name, 
   CASE WHEN u.base_group = g.group_id THEN 1 ELSE 0 END AS IsBaseGroup
FROM 
  users u
  JOIN 
  assigned_groups ag ON u.user_id = ag.user_id
  JOIN
  user_groups g ON ag.group_id  = g.group_id 
WHERE 
  u.user_id = :user_id

Upvotes: 1

rauschen
rauschen

Reputation: 3996

Have fun with it

SELECT g.group_id, g.name
FROM groups g
LEFT JOIN assigned_groups ag 
      ON  ag.group_id=g.group_id
LEFT JOIN users u
      ON  u.base_group=g.group_id
WHERE u.user_id=:user_id OR ag.user_id=:user_id

Upvotes: -1

NPE
NPE

Reputation: 500357

In your query, you're applying LEFT JOIN to users u on the left and assigned_group ag on the right. user_groups g is not part of the outer join and cannot be referenced in the ON clause.

Did you mean something like:

SELECT g.group_id, g.name
FROM users u,
     user_groups g LEFT JOIN assigned_groups ag
         ON ag.user_id=:user_id AND ag.group_id=g.group_id
WHERE u.base_group=g.group_id AND u.user_id=:user_id

Upvotes: 1

Related Questions