Hi, I work with a rel. 3.23.36 of MYSQL and I have found this problem:
I have two tables named t_payments and t_payment_lang; these are their descriptions and data: t_payments (description): +-----------------+---------------+------+-----+---------+---------------- + | Field | Type | Null | Key | Default | Extra | +-----------------+---------------+------+-----+---------+---------------- + | pay_key | int(11) | | PRI | NULL | auto_increment | | pay_enabled | tinyint(1) | YES | | 0 | | | pay_fixed_comm | decimal(18,4) | YES | | 0.0000 | | | pay_perc_comm | decimal(18,4) | YES | | 0.0000 | | | pay_step_height | decimal(18,4) | YES | | 0.0000 | | | pay_step_width | decimal(18,4) | YES | | 0.0000 | | | pay_tax_id | int(11) | YES | | 1 | | | pay_default | tinyint(1) | YES | | NULL | | | pay_en_download | tinyint(1) | YES | | NULL | | +-----------------+---------------+------+-----+---------+---------------- + t_payments (data, some fields) +---------+-------------+----------------+-------------+-----------------+ | pay_key | pay_enabled | pay_fixed_comm | pay_default | pay_en_download | +---------+-------------+----------------+-------------+-----------------+ | 1 | 1 | 0.0000 | 1 | 1 | | 2 | 1 | 0.0000 | 0 | 1 | | 3 | 0 | 0.0000 | 0 | NULL | | 4 | 1 | 0.0000 | 1 | NULL | +---------+-------------+----------------+-------------+-----------------+ t_payment_lang (description): +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | pyl_key | int(11) | | PRI | NULL | auto_increment | | pyl_pay_id | int(11) | | | 0 | | | pyl_lang_id | int(11) | | | 0 | | | pyl_description | varchar(100) | YES | | NULL | | +-----------------+--------------+------+-----+---------+----------------+ t_payment_lang (data) +---------+------------+-------------+------------------+ | pyl_key | pyl_pay_id | pyl_lang_id | pyl_description | +---------+------------+-------------+------------------+ | 1 | 1 | 1 | Cash on delivery | | 17 | 2 | 1 | Credit Card | | 18 | 3 | 1 | Credit Card 2 | +---------+------------+-------------+------------------+ The result of this query: SELECT pay_key, pyl_description, pay_en_download FROM t_payments,t_payment_lang WHERE t_payments.pay_key = t_payment_lang.pyl_pay_id AND t_payment_lang.pyl_lang_id = 1 AND t_payments.pay_enabled <> 0; is: +---------+------------------+-----------------+ | pay_key | pyl_description | pay_en_download | +---------+------------------+-----------------+ | 1 | Cash on delivery | NULL | | 2 | Credit Card | NULL | +---------+------------------+-----------------+ the correct (aspected!) result is: +---------+------------------+-----------------+ | pay_key | pyl_description | pay_en_download | +---------+------------------+-----------------+ | 1 | Cash on delivery | 1 | | 2 | Credit Card | 1 | +---------+------------------+-----------------+ I obtain the correct result if i write the same query in this way: SELECT pay_key, pyl_description, pay_en_download FROM t_payments LEFT JOIN t_payment_lang ON t_payments.pay_key = t_payment_lang.pyl_pay_id WHERE t_payment_lang.pyl_lang_id = 1 AND t_payments.pay_enabled <> 0; or if I drop the column pay_fixed_comm column from table t_payments. I don't understand why ! Thanks for any advice. Luca --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php