Problema with left join

2005-12-16 Thread serrano . neves 

Hi,

i have a problem, with my one query, this query is OK, in mysql 3.23, 
but i update my database to mysql 5.0 and one query having a error


SELECT count( p.products_id )  AS total
FROM products_description pd, products p, products_to_categories p2c
LEFT  JOIN manufacturers m
USING ( p.manufacturers_id = m.manufacturers_id )
LEFT  JOIN specials s
USING ( p.products_id = s.products_id )
WHERE p.products_status = '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id = '4'
AND p2c.categories_id = '16'

the error

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near '.manufacturers_id = m.manufacturers_id )

 LEFT JOIN specials s
 USING ( p.produc' at line 4

What's the error in the query?!!?

Thanks

Eduardo Jorge (aka etho)

Re: Problema with left join

2005-12-16 Thread Jigal van Hemert 

[EMAIL PROTECTED] wrote:
#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near '.manufacturers_id = m.manufacturers_id )

 LEFT JOIN specials s
 USING ( p.produc' at line 4

What's the error in the query?!!?


USING ( list_of_column_names )
versus
ON  expression

AFAIK it would be:
USING (products_id)
or
ON p.products_id = s.products_id
etc.

Regards, Jigal.


smime.p7s
Description: S/MIME Cryptographic Signature

Re: Problema with left join

2005-12-16 Thread serrano . neves 

Hi,

I resolve my problem...

select count(p.products_id) as total from products_description pd, 
products p left join manufacturers m using (manufacturers_id), 
products_to_categories p2c left join specials s using (products_id) 
where p.products_status = '1' and p.products_id = p2c.products_id and 
pd.products_id = p2c.products_id and pd.language_id = '4' and 
p2c.categories_id = '16'


that`s ok :)

Thanks

Em 16/12/2005, às 11:40, Jigal van Hemert escreveu:


[EMAIL PROTECTED] wrote:
#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near '.manufacturers_id = m.manufacturers_id )

 LEFT JOIN specials s
 USING ( p.produc' at line 4
What's the error in the query?!!?


USING ( list_of_column_names )
versus
ON  expression

AFAIK it would be:
USING (products_id)
or
ON p.products_id = s.products_id
etc.

Regards, Jigal.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problema with left join

2005-12-16 Thread Jigal van Hemert 

[EMAIL PROTECTED] wrote:


Hi,

I resolve my problem...

select count(p.products_id) as total from products_description pd, 
products p left join manufacturers m using (manufacturers_id), 
products_to_categories p2c left join specials s using (products_id) 
where p.products_status = '1' and p.products_id = p2c.products_id and 
pd.products_id = p2c.products_id and pd.language_id = '4' and 
p2c.categories_id = '16'




Read:
http://dev.mysql.com/doc/refman/5.0/en/join.html
especially the part starting with: Before MySQL 5.0.12, the comma 
operator (,) and JOIN both had the same precedence (...).


It may be wiser to replace the comma-operator by an explicit JOIN to 
prevent any problems with precedence, using aliases before they are 
declared, etc.
This part of the manual explains the changes made in MySQL regarding the 
handling of joins in MySQL 5.


Regards, Jigal.


smime.p7s
Description: S/MIME Cryptographic Signature