Left Join Error

[mattclements]

Hey all,

This used to work, and with a change from MySQL 4 to MySQL 5 it broke - I belive it is a bug currently being fixed:

select prod_id, prod_name, prod_cat, cat_name, man_name, prod_price, prod_date, prod_seq, prod_default, type_name from prods, cats, mans left join types on prod_type=type_id where prod_cat=cat_id and prod_man=man_id order by cat_seq, cat_name, prod_seq, prod_name

 

causes:

Unknown column 'prod_type' in 'on clause'

 

Any idea how to rewrite the code to fix this?

 

Regards,

Matt

[Mchl]

Check that the column actually exists in the table.

 

Hint: use aliases for greater readability of your queries.

Hint2: avoid joins through comma. Better use INNER JOIN

 

 

[mattclements]

prod_type exists in the prods table.

 

Hint: use aliases for greater readability of your queries - Please explain

Hint2: avoid joins through comma. Better use INNER JOIN - as far as im aware I am not joining through comma

[Mchl]

1.

Like this:

FROM prods AS p LEFT JOIN types AS t ON p.prod_type = t.type_id

 

2.

from prods, cats, mans left join types

if these are not commas...

[mattclements]

So - My updated query is:

select p.prod_id, p.prod_name, p.prod_cat, c.cat_name, m.man_name, p.prod_price, p.prod_date, p.prod_seq, p.prod_default, t.type_name
from prods AS p, cats AS c, mans AS m
left join types AS t on p.prod_type=t.type_id
where prod_cat=cat_id
and prod_man=man_id
order by cat_seq, cat_name, prod_seq, prod_name

 

Still causing:

Unknown column 'p.prod_type' in 'on clause' (Error 1054)

 

How can I avoid using commas?

 

Regards,

Matt

[mattclements]

select p.prod_id, p.prod_name, p.prod_cat, c.cat_name, m.man_name, p.prod_price, p.prod_date, p.prod_seq, p.prod_default, t.type_name
from (prods AS p, cats AS c, mans AS m)
left join types AS t on p.prod_type=t.type_id
where prod_cat=cat_id
and prod_man=man_id
order by cat_seq, cat_name, prod_seq, prod_name

 

Fixed!

 

Cheers,

Matt

[Mchl]

from (prods AS p, cats AS c, mans AS m)
left join types AS t on p.prod_type=t.type_id
where prod_cat=cat_id
and prod_man=man_id

 

is equivalent to

 

FROM
  prods AS p
INNER JOIN
  mans AS m
ON 
  m.man_id = p.prod_man
INNER JOIN
  cats AS c
ON
  c.cat_id = p.prod_cat
LEFT JOIN
  types AS t
ON
  t.type_id = p.prod_type

 

This way MySQL will know exactly which tables you want to join and on what conditions.